﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.DBM;
using KPIS.GERP.GBMS.DAL;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DALFactory;
using KPIS.GERP.GBMS.IBLL;
using System.Globalization;

namespace KPIS.GERP.GBMS.BLL
{
    public sealed class ManageProject
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataTable GetDataProjectBySequence(string seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@seq", seq);

                string sql = "select r.bgm_proj_seq as Sequence, r.bgm_proj_code, r.bgm_proj_name"
                            + ", r.bgm_year"
                            + ", s.bgm_strat_list_name, j.job_site_name"
                            + " from bgm_project r"
                                + " left join bgm_strategy_data_list s on r.bgm_strat_list_seq = s.bgm_strat_list_seq"
                                + " left join hrm_mp_job_sites j on r.job_site_seq = j.job_site_seq"
                            + " where r.bgm_proj_seq = @seq";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetDataProjectByYear(int Year, string jobsite)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@DeleteStatus", SystemInfo._DeleteRecordStatus);   // prj
                dbManager.AddParameters(1, "@ApproveStatus", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(2, "@Year", Year);
                dbManager.AddParameters(3, "@jobsite", jobsite);

                string sql = "select p.bgm_proj_seq as Sequence, p.bgm_proj_code"
                    + ", p.bgm_proj_name, s.bgm_strat_list_name as Strategy, p.bgm_strat_list_seq"
                    + ", s.bgm_strat_list_name, j.job_site_name"

                    + ", coalesce(pe.amount, 0) as proj_amount"
                    + ", coalesce(prpt1.amount, 0) as proj_transfer_amount"
                    + ", coalesce(prpt2.amount, 0) as proj_reamin_amount"
                    + ", coalesce(o.amount, 0) as oper_amount"
                    //+ ", coalesce(orpt2.amount, 0) as oper__amount"
                    + ", coalesce(orpt.amount, 0) as oper_remain_amount"

                    + " from bgm_project p"
                    + " left join bgm_strategy_data_list s on p.bgm_strat_list_seq = s.bgm_strat_list_seq"
                    + " left join hrm_mp_job_sites j on p.job_site_seq = j.job_site_seq"
                    + " left join (select bgm_proj_seq"
                        + ", sum(coalesce(total_amount, 0)) as amount"
                        + " from bgm_project_expense"
                        + " group by bgm_proj_seq"
                        + ") pe on p.bgm_proj_seq = pe.bgm_proj_seq"

                    + " left join (select pe2.bgm_proj_seq"
                        + ", sum(coalesce(prpt.cal_amount, 0)) as amount"
                        + " from bgm_project_report prpt"
                        + " inner join bgm_project_expense pe2 on prpt.bgm_proj_exp_seq = pe2.bgm_proj_exp_seq"
                        + " where prpt.record_status not like @DeleteStatus"
                        + " and prpt.rpt_type in (2, 3)"
                        + " group by pe2.bgm_proj_seq"
                        +") prpt1 on p.bgm_proj_seq = prpt1.bgm_proj_seq"

                    + " left join (select pe2.bgm_proj_seq"
                        + ", sum(coalesce(prpt.cal_amount, 0)) as amount"
                        + " from bgm_project_report prpt"
                        + " inner join bgm_project_expense pe2 on prpt.bgm_proj_exp_seq = pe2.bgm_proj_exp_seq"
                        + " where prpt.record_status not like @DeleteStatus"
                        + " group by pe2.bgm_proj_seq"
                        +") prpt2 on p.bgm_proj_seq = prpt2.bgm_proj_seq"

                    + " left join (select o.bgm_proj_seq"
                        + ", sum(coalesce(oe.bath_amount, 0)) as amount"
                        + " from bgm_project_operation o"
                        + " inner join bgm_project_operation_expense oe on o.bgm_proj_oper_seq = oe.bgm_proj_oper_seq"
                        + " where o.record_status = @ApproveStatus"
                        + " group by o.bgm_proj_seq"
                        +") o on p.bgm_proj_seq = o.bgm_proj_seq"

                    + " left join (select o.bgm_proj_seq"
                        + ", sum(coalesce(orpt.cal_amount, 0)) as amount"
                        + " from bgm_project_operation_report orpt"
                        + " inner join bgm_project_operation_expense oe on orpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq"
                        + " inner join bgm_project_operation o on oe.bgm_proj_oper_seq = o.bgm_proj_oper_seq"
                        + " where orpt.record_status not like @DeleteStatus"
                        + " group by o.bgm_proj_seq"
                        +") orpt on p.bgm_proj_seq = orpt.bgm_proj_seq"

                    + " where p.record_status = @ApproveStatus"
                    + ((jobsite.Length > 0) ? " and p.job_site_seq = @jobsite" : "")
                    + " and p.bgm_year = @Year"
                    + " order by p.bgm_proj_seq";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectByYear2(int Year, string jobsite, string bgm_proj_seq)
        {
            try
            {
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@DeleteStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@ApproveStatus", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(2, "@Year", Year);
                dbManager.AddParameters(3, "@jobsite", jobsite);
                dbManager.AddParameters(4, "@bgm_proj_seq", bgm_proj_seq);

                string sql = "select p.bgm_proj_seq, p.bgm_proj_code, p.bgm_proj_name"
                    + ", s1.bgm_strat_list_name as bgm_subact_name, s1.bgm_strat_list_seq as bgm_subact_seq"
                    + ", s2.bgm_strat_list_name as bgm_act_name, s2.bgm_strat_list_seq as bgm_act_seq"
                    + ", s3.bgm_strat_list_name as bgm_output_name, s3.bgm_strat_list_seq as bgm_output_seq"
                    //+ ", pe.bgm_source_seq, s.bgm_source_name"
                    //+ ", pe.bgm_exp_seq, e.bgm_exp_name"
                    //+ ", e.bgm_exp_grp_seq, eg.bgm_exp_grp_name"
                    + ", j.job_site_name, p.record_status"
                    + ", case p.record_status when 'D' then 'ปิดโครงการ'"
                        + " else rs.record_status_name"
                        + " end as record_status_name"

                    //+ ", coalesce(pe.total_amount, 0) as proj_amount"
                    //+ ", coalesce(rpt1.rpt_amount, 0) as rpt_amount1"
                    //+ ", coalesce(rpt2.rpt_amount, 0) as rpt_amount2"
                    //+ ", - coalesce(rpt3.rpt_amount, 0) as rpt_amount3"
                    //+ ", - coalesce(rpt4.rpt_amount, 0) as rpt_amount4"
                    //+ ", - coalesce(rpt5.rpt_amount, 0) as rpt_amount5"
                    //+ ", coalesce(rpt6.rpt_amount, 0) as rpt_amount6"
                    //+ ", coalesce(rpt7.rpt_amount, 0) as rpt_amount7"
                    //+ ", coalesce(rpt8.rpt_amount, 0) as rpt_amount8"

                    + ", sum(coalesce(pe.total_amount, 0)) as proj_amount"
                    + ", sum(coalesce(rpt1.rpt_amount, 0)) as rpt_amount1"
                    + ", sum(coalesce(rpt2.rpt_amount, 0)) as rpt_amount2"
                    + ", sum(- coalesce(rpt3.rpt_amount, 0)) as rpt_amount3"
                    + ", sum(- coalesce(rpt4.rpt_amount, 0)) as rpt_amount4"
                    + ", sum(- coalesce(rpt5.rpt_amount, 0)) as rpt_amount5"
                    + ", sum(coalesce(rpt6.rpt_amount, 0)) as rpt_amount6"
                    + ", sum(coalesce(rpt7.rpt_amount, 0)) as rpt_amount7"
                    + ", sum(coalesce(rpt8.rpt_amount, 0)) as rpt_amount8"

                    + " from bgm_project p"
                    + " left join bgm_strategy_data_mapping map1 on p.bgm_strat_list_seq = map1.bgm_strat_list_seq"
                        + " and map1.record_status not like @DeleteStatus"
                        + " and map1.bgm_year = @Year"
                    + " left join bgm_strategy_data_mapping map2 on map1.upper_bgm_strat_data_map_seq = map2.bgm_strat_data_map_seq"
                        + " and map2.record_status not like @DeleteStatus"
                        + " and map2.bgm_year = @Year"
                    + " left join bgm_strategy_data_mapping map3 on map2.upper_bgm_strat_data_map_seq = map3.bgm_strat_data_map_seq"
                        + " and map3.record_status not like @DeleteStatus"
                        + " and map3.bgm_year = @Year"

                    + " left join bgm_strategy_data_list s1 on p.bgm_strat_list_seq = s1.bgm_strat_list_seq"
                    + " left join bgm_strategy_data_list s2 on map2.bgm_strat_list_seq = s2.bgm_strat_list_seq"
                    + " left join bgm_strategy_data_list s3 on map3.bgm_strat_list_seq = s3.bgm_strat_list_seq"

                    + " left join hrm_mp_job_sites j on p.job_site_seq = j.job_site_seq"
                    + " left join bgm_project_expense pe on p.bgm_proj_seq = pe.bgm_proj_seq"
                    + " left join sys_record_status rs on p.record_status = rs.record_status"
                    //+ " left join bgm_mas_source s on pe.bgm_source_seq = s.bgm_source_seq"
                    //+ " left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq"
                    //+ " left join bgm_mas_expense_group eg on e.bgm_exp_grp_seq = eg.bgm_exp_grp_seq"

                    + " left join (select bgm_proj_exp_seq"
                        + ", sum(coalesce(cal_amount, 0)) as rpt_amount"
                        + " from bgm_project_report"
                        + " where record_status not like @DeleteStatus"
                        + " and rpt_type in (2,3)"
                        + " group by bgm_proj_exp_seq"
                        + ") rpt1 on pe.bgm_proj_exp_seq = rpt1.bgm_proj_exp_seq"

                    + " left join (select oe.bgm_proj_exp_seq"
                        + ", sum(coalesce(oe.bath_amount, 0)) as rpt_amount"
                        + " from bgm_project_operation o"
                        + " inner join bgm_project_operation_expense oe on o.bgm_proj_oper_seq = oe.bgm_proj_oper_seq"
                        + " where o.record_status = @ApproveStatus"
                        + " group by oe.bgm_proj_exp_seq"
                        + ") rpt2 on pe.bgm_proj_exp_seq = rpt2.bgm_proj_exp_seq"

                    + " left join (select oe.bgm_proj_exp_seq"
                        + ", sum(coalesce(rpt.cal_amount, 0)) as rpt_amount"
                        + " from bgm_project_operation_report rpt"
                        + " left join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq"
                        + " where rpt.record_status not like @DeleteStatus"
                        + " and rpt.rpt_type in (4, 5)"
                        + " group by oe.bgm_proj_exp_seq"
                        + ") rpt3 on pe.bgm_proj_exp_seq = rpt3.bgm_proj_exp_seq"

                    + " left join (select oe.bgm_proj_exp_seq"
                        + ", sum(coalesce(rpt.cal_amount, 0)) as rpt_amount"
                        + " from bgm_project_operation_report rpt"
                        + " left join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq"
                        + " where rpt.record_status not like @DeleteStatus"
                        + " and rpt.rpt_type = 6"
                        + " group by oe.bgm_proj_exp_seq"
                        + ") rpt4 on pe.bgm_proj_exp_seq = rpt4.bgm_proj_exp_seq"

                    + " left join (select oe.bgm_proj_exp_seq"
                        + ", sum(coalesce(rpt.cal_amount, 0)) as rpt_amount"
                        + " from bgm_project_operation_report rpt"
                        + " left join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq"
                        + " where rpt.record_status not like @DeleteStatus"
                        + " and rpt.rpt_type = 7"
                        + " group by oe.bgm_proj_exp_seq"
                        + ") rpt5 on pe.bgm_proj_exp_seq = rpt5.bgm_proj_exp_seq"

                    + " left join (select oe.bgm_proj_exp_seq"
                        + ", sum(coalesce(rpt.cal_amount, 0)) as rpt_amount"
                        + " from bgm_project_operation_report rpt"
                        + " left join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq"
                        + " where rpt.record_status not like @DeleteStatus"
                        + " and rpt.rpt_type = 8"
                        + " group by oe.bgm_proj_exp_seq"
                        + ") rpt6 on pe.bgm_proj_exp_seq = rpt6.bgm_proj_exp_seq"

                    + " left join (select oe.bgm_proj_exp_seq"
                        + ", sum(coalesce(rpt.cal_amount, 0)) as rpt_amount"
                        + " from bgm_project_operation_report rpt"
                        + " left join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq"
                        + " where rpt.record_status not like @DeleteStatus"
                        + " group by oe.bgm_proj_exp_seq"
                        + ") rpt7 on pe.bgm_proj_exp_seq = rpt7.bgm_proj_exp_seq"

                    + " left join (select bgm_proj_exp_seq"
                        + ", sum(coalesce(cal_amount, 0)) as rpt_amount"
                        + " from bgm_project_report"
                        + " where record_status not like @DeleteStatus"
                        + " group by bgm_proj_exp_seq"
                        + ") rpt8 on pe.bgm_proj_exp_seq = rpt8.bgm_proj_exp_seq"

                    + " where p.record_status in (@ApproveStatus, @DeleteStatus)"
                    + (!string.IsNullOrEmpty(jobsite) ? " and p.job_site_seq = @jobsite" : "")
                    + (!string.IsNullOrEmpty(bgm_proj_seq) ? " and p.bgm_proj_seq = @bgm_proj_seq" : "")
                    + " and p.bgm_year = @Year"
                    +" group by p.bgm_proj_seq, p.bgm_proj_code, p.bgm_proj_name"
                    + ", s1.bgm_strat_list_name, s1.bgm_strat_list_seq"
                    + ", s2.bgm_strat_list_name, s2.bgm_strat_list_seq"
                    + ", s3.bgm_strat_list_name, s3.bgm_strat_list_seq"
                    //+ ", pe.bgm_source_seq, s.bgm_source_name"
                    //+ ", pe.bgm_exp_seq, e.bgm_exp_name"
                    //+ ", e.bgm_exp_grp_seq, eg.bgm_exp_grp_name"
                    + ", j.job_site_name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataSourceByProj(string strBgmProjSeq)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@DeleteStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@ApproveStatus", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(2, "@bgm_proj_seq", strBgmProjSeq);

                string sql = "select p.bgm_proj_seq, pe.bgm_source_seq, s.bgm_source_name"

                    + ", sum(coalesce(pe.amount, 0)) as proj_amount"
                    + ", sum(coalesce(prpt1.amount, 0)) as proj_transfer_amount"
                    + ", sum(coalesce(prpt2.amount, 0)) as proj_reamin_amount"
                    + ", sum(coalesce(o.amount, 0)) as oper_amount"
                    + ", sum(coalesce(orpt.amount, 0)) as oper_remain_amount"

                    + " from bgm_project p"
                    + " left join (select bgm_proj_seq, bgm_source_seq"
                        + ", sum(coalesce(total_amount, 0)) as amount"
                        + " from bgm_project_expense"
                        + " group by bgm_proj_seq, bgm_source_seq"
                        + ") pe on p.bgm_proj_seq = pe.bgm_proj_seq"
                    + " left join bgm_mas_source s on pe.bgm_source_seq = s.bgm_source_seq"

                    + " left join (select pe2.bgm_proj_seq, pe2.bgm_source_seq"
                        + ", sum(coalesce(prpt.cal_amount, 0)) as amount"
                        + " from bgm_project_report prpt"
                        + " inner join bgm_project_expense pe2 on prpt.bgm_proj_exp_seq = pe2.bgm_proj_exp_seq"
                        + " where prpt.record_status not like @DeleteStatus"
                        + " and prpt.rpt_type in (2, 3)"
                        + " group by pe2.bgm_proj_seq, pe2.bgm_source_seq"
                        + ") prpt1 on p.bgm_proj_seq = prpt1.bgm_proj_seq"
                            + " and pe.bgm_source_seq = prpt1.bgm_source_seq"

                    + " left join (select pe2.bgm_proj_seq, pe2.bgm_source_seq"
                        + ", sum(coalesce(prpt.cal_amount, 0)) as amount"
                        + " from bgm_project_report prpt"
                        + " inner join bgm_project_expense pe2 on prpt.bgm_proj_exp_seq = pe2.bgm_proj_exp_seq"
                        + " where prpt.record_status not like @DeleteStatus"
                        + " group by pe2.bgm_proj_seq, pe2.bgm_source_seq"
                        + ") prpt2 on p.bgm_proj_seq = prpt2.bgm_proj_seq"
                            + " and pe.bgm_source_seq = prpt2.bgm_source_seq"

                    + " left join (select o.bgm_proj_seq, pe2.bgm_source_seq"
                        + ", sum(coalesce(oe.bath_amount, 0)) as amount"
                        + " from bgm_project_operation o"
                        + " inner join bgm_project_operation_expense oe on o.bgm_proj_oper_seq = oe.bgm_proj_oper_seq"
                        + " inner join bgm_project_expense pe2 on oe.bgm_proj_exp_seq = pe2.bgm_proj_exp_seq"
                        + " where o.record_status = @ApproveStatus"
                        + " group by o.bgm_proj_seq, pe2.bgm_source_seq"
                        + ") o on p.bgm_proj_seq = o.bgm_proj_seq"
                            + " and pe.bgm_source_seq = o.bgm_source_seq"

                    + " left join (select o.bgm_proj_seq, pe2.bgm_source_seq"
                        + ", sum(coalesce(orpt.cal_amount, 0)) as amount"
                        + " from bgm_project_operation_report orpt"
                        + " inner join bgm_project_operation_expense oe on orpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq"
                        + " inner join bgm_project_operation o on oe.bgm_proj_oper_seq = o.bgm_proj_oper_seq"
                        + " inner join bgm_project_expense pe2 on oe.bgm_proj_exp_seq = pe2.bgm_proj_exp_seq"
                        + " where orpt.record_status not like @DeleteStatus"
                        + " group by o.bgm_proj_seq, pe2.bgm_source_seq"
                        + ") orpt on p.bgm_proj_seq = orpt.bgm_proj_seq"
                            + " and pe.bgm_source_seq = orpt.bgm_source_seq"

                    + " where p.record_status = @ApproveStatus"
                    + " and p.bgm_proj_seq = @bgm_proj_seq"
                    + " group by p.bgm_proj_seq, pe.bgm_source_seq, s.bgm_source_name"
                    + " order by pe.bgm_source_seq";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetReportTreeList(string bgm_proj_seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@bgm_proj_seq", bgm_proj_seq);

                string sql = "select 1 as union_block, concat(cast(s.bgm_source_seq as char), '-SOURCE-', cast(s.bgm_source_seq as char)) as child_value"
                    + ", s.bgm_source_name as text"
                    + ", null as parent_value"
                    + ", pe.bgm_source_seq"
                    + ", sum(coalesce(rpt.cal_amount, 0)) as cal_amount"
                    + " from bgm_project_report rpt"
                    + " left join bgm_project_expense pe on rpt.bgm_proj_exp_seq = pe.bgm_proj_exp_seq"
                    + " left join bgm_mas_source s on pe.bgm_source_seq = s.bgm_source_seq"
                    + " where pe.bgm_proj_seq = @bgm_proj_seq"
                    + " and rpt.record_status <> 'D'"
                    + " group by s.bgm_source_seq, s.bgm_source_name, pe.bgm_source_seq"

                    + " union all"

                    + " select 2 as union_block, concat(cast(pe.bgm_source_seq as char), '-GROUP-', cast(eg.bgm_exp_grp_seq as char)) as child_value"
                    + ", eg.bgm_exp_grp_name as text"
                    + ", concat(cast(pe.bgm_source_seq as char), '-SOURCE-', cast(pe.bgm_source_seq as char)) as parent_value"
                    + ", pe.bgm_source_seq"
                    + ", sum(coalesce(rpt.cal_amount, 0)) as cal_amount"
                    + " from bgm_project_report rpt"
                    + " left join bgm_project_expense pe on rpt.bgm_proj_exp_seq = pe.bgm_proj_exp_seq"
                    + " left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq"
                    + " left join bgm_mas_expense_group eg on e.bgm_exp_grp_seq = eg.bgm_exp_grp_seq"
                    + " where pe.bgm_proj_seq = @bgm_proj_seq"
                    + " and rpt.record_status <> 'D'"
                    + " group by eg.bgm_exp_grp_seq, eg.bgm_exp_grp_name, pe.bgm_source_seq"

                    + " union all"

                    + " select 3 as union_block, concat(cast(pe.bgm_source_seq as char), '-EXPENSE-', cast(e2.bgm_exp_seq as char)) as child_value"
                    + ", e2.bgm_exp_name as text"
                    + ", concat(cast(pe.bgm_source_seq as char), '-GROUP-', cast(e2.bgm_exp_grp_seq as char)) as parent_value"
                    + ", pe.bgm_source_seq"
                    + ", sum(coalesce(rpt.cal_amount, 0)) as cal_amount"
                    + " from bgm_project_report rpt"
                    + " left join bgm_project_expense pe on rpt.bgm_proj_exp_seq = pe.bgm_proj_exp_seq"
                    + " left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq"
                    + " left join bgm_mas_expense e1 on e.upper_bgm_exp_seq = e1.bgm_exp_seq"
                    + " left join bgm_mas_expense e2 on e1.upper_bgm_exp_seq = e2.bgm_exp_seq"
                    + " where pe.bgm_proj_seq = @bgm_proj_seq"
                    + " and rpt.record_status <> 'D'"
                    + " group by e2.bgm_exp_seq, e2.bgm_exp_name, e2.bgm_exp_grp_seq, pe.bgm_source_seq"

                    + " union all"

                    + " select 4 as union_block, concat(cast(pe.bgm_source_seq as char), '-EXPENSE-', cast(e1.bgm_exp_seq as char)) as child_value"
                    + ", e1.bgm_exp_name as text"
                    + ", concat(cast(pe.bgm_source_seq as char), '-EXPENSE-', cast(e1.upper_bgm_exp_seq as char)) as parent_value"
                    + ", pe.bgm_source_seq"
                    + ", sum(coalesce(rpt.cal_amount, 0)) as cal_amount"
                    + " from bgm_project_report rpt"
                    + " left join bgm_project_expense pe on rpt.bgm_proj_exp_seq = pe.bgm_proj_exp_seq"
                    + " left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq"
                    + " left join bgm_mas_expense e1 on e.upper_bgm_exp_seq = e1.bgm_exp_seq"
                    + " where pe.bgm_proj_seq = @bgm_proj_seq"
                    + " and rpt.record_status <> 'D'"
                    + " group by e1.bgm_exp_seq, e1.bgm_exp_name, e1.upper_bgm_exp_seq, pe.bgm_source_seq"

                    + " union all"

                    + " select 5 as union_block, concat(cast(pe.bgm_source_seq as char), '-EXPENSE-', cast(e.bgm_exp_seq as char)) as child_value"
                    + ", e.bgm_exp_name as text"
                    + ", concat(cast(pe.bgm_source_seq as char), '-EXPENSE-', cast(e.upper_bgm_exp_seq as char)) as parent_value"
                    + ", pe.bgm_source_seq"
                    + ", sum(coalesce(rpt.cal_amount, 0)) as cal_amount"
                    + " from bgm_project_report rpt"
                    + " left join bgm_project_expense pe on rpt.bgm_proj_exp_seq = pe.bgm_proj_exp_seq"
                    + " left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq"
                    + " where pe.bgm_proj_seq = @bgm_proj_seq"
                    + " and rpt.record_status <> 'D'"
                    + " group by e.bgm_exp_seq, e.bgm_exp_name, e.upper_bgm_exp_seq, pe.bgm_source_seq";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetTotalReport(string seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@seq", seq);

                string sql = "select r.bgm_proj_seq as Sequence, re.bgm_source_seq, s.bgm_source_name"
                            + ", coalesce(sum(re.total_amount), 0) as total_amount"
                            + ", (select coalesce(sum(cal_amount), 0) from bgm_project_report s1 inner join bgm_project_expense s2 on s1.bgm_proj_exp_seq = s2.bgm_proj_exp_seq where s2.bgm_proj_seq = r.bgm_proj_seq and s2.bgm_source_seq = re.bgm_source_seq) as remain_amount"
                            + " from bgm_project r"
                                + " left join bgm_project_expense re on re.bgm_proj_seq = r.bgm_proj_seq"
                                + " left join bgm_mas_source s on s.bgm_source_seq = re.bgm_source_seq"
                            + " where r.bgm_proj_seq = @seq"
                            + " group by r.bgm_proj_seq, re.bgm_source_seq, s.bgm_source_name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetProjectExpenseWithRemainAmount(string bgm_proj_seq, string bgm_source_seq, string bgm_exp_grp_seq)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@bgm_proj_seq", bgm_proj_seq);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_exp_grp_seq", bgm_exp_grp_seq);

                string sql = "select re.bgm_proj_exp_seq, re.bgm_source_seq, r.bgm_strat_list_seq, r.job_site_seq"
                    + ", concat(exp1.bgm_exp_name, '/', exp2.bgm_exp_name, '/', exp3.bgm_exp_name) as bgm_proj_exp_name"
                    + ", exp1.bgm_exp_grp_seq, exp1.bgm_exp_name, re.total_amount, rpt.cal_amount"
                    + " from bgm_project r"
                    + " left join bgm_project_expense re on re.bgm_proj_seq = r.bgm_proj_seq"
                    + " left join bgm_mas_expense exp1 on re.bgm_exp_seq = exp1.bgm_exp_seq"
                    + " left join bgm_mas_expense exp2 on exp1.upper_bgm_exp_seq = exp2.bgm_exp_seq"
                    + " left join bgm_mas_expense exp3 on exp2.upper_bgm_exp_seq = exp3.bgm_exp_seq"
                    + " left join (select bgm_proj_exp_seq"
                        + ", sum(coalesce(cal_amount, 0)) as cal_amount"
                        + " from bgm_project_report"
                        + " where record_status not like 'D'"
                        + " group by bgm_proj_exp_seq"
                        + ") rpt on re.bgm_proj_exp_seq = rpt.bgm_proj_exp_seq"
                    + " where r.bgm_proj_seq = @bgm_proj_seq"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " and re.bgm_source_seq = @bgm_source_seq" : "")
                    + (!string.IsNullOrEmpty(bgm_exp_grp_seq) ? " and exp1.bgm_exp_grp_seq = @bgm_exp_grp_seq" : "");
                    //+ " group by exp1.bgm_exp_grp_seq, exp1.bgm_exp_name, exp2.bgm_exp_name, exp3.bgm_exp_name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetProjectExpense(string bgm_proj_seq, string bgm_source_seq, string bgm_exp_grp_seq)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@bgm_proj_seq", bgm_proj_seq);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_exp_grp_seq", bgm_exp_grp_seq);

                string sql = "select re.bgm_proj_exp_seq as value"
                    + ", concat(exp1.bgm_exp_name, '/', exp2.bgm_exp_name, '/', exp3.bgm_exp_name) as text"
                    + " from bgm_project r"
                    + " left join bgm_project_expense re on re.bgm_proj_seq = r.bgm_proj_seq"
                    + " left join bgm_mas_expense exp1 on re.bgm_exp_seq = exp1.bgm_exp_seq"
                    + " left join bgm_mas_expense exp2 on exp1.upper_bgm_exp_seq = exp2.bgm_exp_seq"
                    + " left join bgm_mas_expense exp3 on exp2.upper_bgm_exp_seq = exp3.bgm_exp_seq"
                    + " where r.bgm_proj_seq = @bgm_proj_seq"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " and re.bgm_source_seq = @bgm_source_seq" : "")
                    + (!string.IsNullOrEmpty(bgm_exp_grp_seq) ? " and exp1.bgm_exp_grp_seq = @bgm_exp_grp_seq" : "")
                    + " group by re.bgm_proj_exp_seq, exp1.bgm_exp_name, exp2.bgm_exp_name, exp3.bgm_exp_name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetProjectExpenseControl(string bgm_proj_seq, string bgm_source_seq, string bgm_exp_grp_seq, string ctrl_type)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@bgm_proj_seq", bgm_proj_seq);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_exp_grp_seq", bgm_exp_grp_seq);
                dbManager.AddParameters(3, "@ctrl_type", ctrl_type);

                string sql = "select distinct ctrl.bgm_proj_exp_ctrl_seq as value"
                    + ", concat(exp1.bgm_exp_name, '/', exp2.bgm_exp_name, '/', exp3.bgm_exp_name) as text"
                    + " from bgm_project_expense_control ctrl"
                    + " left join bgm_project_expense re on re.bgm_proj_exp_seq = ctrl.bgm_proj_exp_seq"
                    //+ " left join bgm_project r on re.bgm_proj_seq = r.bgm_proj_seq"
                    + " left join bgm_mas_expense exp1 on re.bgm_exp_seq = exp1.bgm_exp_seq"
                    + " left join bgm_mas_expense exp2 on exp1.upper_bgm_exp_seq = exp2.bgm_exp_seq"
                    + " left join bgm_mas_expense exp3 on exp2.upper_bgm_exp_seq = exp3.bgm_exp_seq"
                    + " where re.bgm_proj_seq = @bgm_proj_seq"
                    + " and ctrl.ctrl_type = @ctrl_type"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " and re.bgm_source_seq = @bgm_source_seq" : "")
                    + (!string.IsNullOrEmpty(bgm_exp_grp_seq) ? " and exp1.bgm_exp_grp_seq = @bgm_exp_grp_seq" : "");

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetProjectOperationReport(string bgm_proj_seq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@bgm_proj_seq", bgm_proj_seq);

                string sql = @"
select concat('/', o.bgm_proj_oper_seq) as child_seq
, null as parent_seq
, o.bgm_proj_oper_name as name
, o.bgm_proj_oper_code as code

, coalesce(oe.amount, 0) as operation_amount
--, coalesce(rpt1.cal_amount, 0) as transfer_amount
, coalesce(po.amount, 0) - coalesce(mrpo.amount, 0) as reserve_amount
, coalesce(mr.amount, 0) - coalesce(p.amount, 0) as moneyreq_amount
, coalesce(p.amount, 0) as pay_amount
, coalesce(r.amount, 0) as return_amount
, coalesce(oe.amount, 0)
	- (coalesce(po.amount, 0) - coalesce(mrpo.amount, 0))
	- coalesce(mr.amount, 0) + coalesce(r.amount, 0) as remain_amount
, coalesce(rpt7.amount, 0) as cancel_amount
, rs.record_status_name, o.record_status
from bgm_project_operation o
left join (select oe.bgm_proj_oper_seq
	, sum(coalesce(rpt.cal_amount ,0)) as amount
	from bgm_project_operation_expense oe
	left join bgm_project_operation_report rpt on oe.bgm_proj_oper_exp_seq = rpt.bgm_proj_oper_exp_seq
	where oe.record_status not like 'D'
	and rpt.record_status not like 'D'
	and rpt.rpt_type = 1
	and rpt.cal_amount > 0
	group by oe.bgm_proj_oper_seq
	) oe on o.bgm_proj_oper_seq = oe.bgm_proj_oper_seq

left join (select ope.bgm_proj_oper_seq
	, sum((COALESCE(quo.NetAmount, 0) / COALESCE(quo.Qty, 1)) * ppri.Qty) as amount
	from IcmPurchaseRequestItem pri
  inner join IcmPurchaseRequest pr on pri.PrId = pr.PrId
	inner join IcmBidderQuotation quo on pri.PrItemId = quo.PrItemId
	inner join IcmPurchaseOrder po on quo.BidderId = po.BidderId and po.RecordStatus = 'A'
	inner join (select ppri.PrItemId, ppri.BgSourceId, ppri.ProjOperExpId, sum(ppri.Qty) as Qty
		from IcmPrePurchaseRequestItem ppri
		group by ppri.PrItemId, ppri.BgSourceId, ppri.ProjOperExpId
	) ppri on pri.PrItemId = ppri.PrItemId
	inner join bgm_project_operation_expense ope on ppri.ProjOperExpId = ope.bgm_proj_oper_exp_seq
	where po.RecordStatus = 'A'
	and po.Closed = 0
	and pri.RecordStatus <> 'D'
	group by ope.bgm_proj_oper_seq
) po on o.bgm_proj_oper_seq = po.bgm_proj_oper_seq

left join (select mr.bgm_proj_oper_seq
	, sum(coalesce(mrd.amount, 0)) as amount
	from fim_money_request_dtls mrd
	inner join fim_money_requests mr on mrd.money_req_seq = mr.money_req_seq
	inner join IcmDeliveryNote dn on mr.icm_dn_seq = dn.DnId
	inner join IcmPurchaseOrder po on dn.PoId = po.PoId
	where mr.record_status = 'A'
	and mr.event_seq = 11
	and po.Closed = 0
	and mrd.record_status <> 'D'
	group by mr.bgm_proj_oper_seq
) mrpo on o.bgm_proj_oper_seq = mrpo.bgm_proj_oper_seq

left join (select mr.bgm_proj_oper_seq
	, sum(coalesce(mrd.amount, 0)) as amount
	from fim_money_request_dtls mrd
	inner join fim_money_requests mr on mrd.money_req_seq = mr.money_req_seq
	where mr.record_status = 'A'
	and mrd.record_status <> 'D'
	group by mr.bgm_proj_oper_seq
) mr on o.bgm_proj_oper_seq = mr.bgm_proj_oper_seq

left join (select mr.bgm_proj_oper_seq
	, sum(coalesce(mrd.amount, 0)) as amount
	from fim_payments p
	inner join fim_money_request_dtls mrd on p.money_req_seq = mrd.money_req_seq
	inner join fim_money_requests mr on p.money_req_seq = mr.money_req_seq
	where p.record_status = 'A'
	and mrd.record_status <> 'D'
	group by mr.bgm_proj_oper_seq
) p on o.bgm_proj_oper_seq = p.bgm_proj_oper_seq

left join (select mr.bgm_proj_oper_seq
	, sum(coalesce(r.amount, 0)) as amount
	from fim_receives r
	inner join fim_money_requests mr on r.money_req_seq = mr.money_req_seq
	where r.record_status not in ('D', 'C')
	group by mr.bgm_proj_oper_seq
) r on o.bgm_proj_oper_seq = r.bgm_proj_oper_seq

left join (select oe.bgm_proj_oper_seq
	, sum(coalesce(rpt.cal_amount ,0)) as amount
	from bgm_project_operation_expense oe
	left join bgm_project_operation_report rpt on oe.bgm_proj_oper_exp_seq = rpt.bgm_proj_oper_exp_seq
	where oe.record_status not like 'D'
	and rpt.record_status not like 'D'
	and rpt.rpt_type = 1
	and rpt.cal_amount < 0
	group by oe.bgm_proj_oper_seq
	) rpt7 on o.bgm_proj_oper_seq = rpt7.bgm_proj_oper_seq
left join fim_mas_record_statuses rs on o.record_status = rs.record_status
where o.record_status in ('A', 'C') and o.bgm_proj_seq = @bgm_proj_seq

union all

select concat('/', o.bgm_proj_oper_seq, '/', oe.bgm_source_seq) as child_seq
, concat('/', o.bgm_proj_oper_seq) as parent_seq
, oe.bgm_source_name as name
, oe.bgm_source_code as code

, coalesce(oe.amount, 0) as operation_amount
--, coalesce(rpt1.cal_amount, 0) as transfer_amount
, coalesce(po.amount, 0) - coalesce(mrpo.amount, 0) as reserve_amount
, coalesce(mr.amount, 0) - coalesce(p.amount, 0) as moneyreq_amount
, coalesce(p.amount, 0) as pay_amount
, coalesce(r.amount, 0) as return_amount
, coalesce(oe.amount, 0)
	- (coalesce(po.amount, 0) - coalesce(mrpo.amount, 0))
	- coalesce(mr.amount, 0) + coalesce(r.amount, 0) as remain_amount
, coalesce(rpt7.amount, 0) as cancel_amount
, rs.record_status_name, o.record_status
from bgm_project_operation o
left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, s.bgm_source_code, s.bgm_source_name
	, sum(coalesce(rpt.cal_amount ,0)) as amount
	from bgm_project_operation_expense oe
	left join bgm_project_operation_report rpt on oe.bgm_proj_oper_exp_seq = rpt.bgm_proj_oper_exp_seq
	left join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
	left join bgm_mas_source s on pe.bgm_source_seq = s.bgm_source_seq
	where oe.record_status not like 'D'
	and rpt.record_status not like 'D'
	and rpt.rpt_type = 1
	and rpt.cal_amount > 0
	group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, s.bgm_source_code, s.bgm_source_name
	) oe on o.bgm_proj_oper_seq = oe.bgm_proj_oper_seq

left join (select ope.bgm_proj_oper_seq, pe.bgm_source_seq
	, sum((COALESCE(quo.NetAmount, 0) / COALESCE(quo.Qty, 1)) * ppri.Qty) as amount
	from IcmPurchaseRequestItem pri
  inner join IcmPurchaseRequest pr on pri.PrId = pr.PrId
	inner join IcmBidderQuotation quo on pri.PrItemId = quo.PrItemId
	inner join IcmPurchaseOrder po on quo.BidderId = po.BidderId and po.RecordStatus = 'A'
	inner join (select ppri.PrItemId, ppri.BgSourceId, ppri.ProjOperExpId, sum(ppri.Qty) as Qty
		from IcmPrePurchaseRequestItem ppri
		group by ppri.PrItemId, ppri.BgSourceId, ppri.ProjOperExpId
	) ppri on pri.PrItemId = ppri.PrItemId
	inner join bgm_project_operation_expense ope on ppri.ProjOperExpId = ope.bgm_proj_oper_exp_seq
	left join bgm_project_expense pe on ope.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
	where po.RecordStatus = 'A'
	and po.Closed = 0
	and pri.RecordStatus <> 'D'
	group by ope.bgm_proj_oper_seq, pe.bgm_source_seq
) po on o.bgm_proj_oper_seq = po.bgm_proj_oper_seq
	and oe.bgm_source_seq = po.bgm_source_seq

left join (select mr.bgm_proj_oper_seq, mr.bgm_source_seq
	, sum(coalesce(mrd.amount, 0)) as amount
	from fim_money_request_dtls mrd
	inner join fim_money_requests mr on mrd.money_req_seq = mr.money_req_seq
	inner join IcmDeliveryNote dn on mr.icm_dn_seq = dn.DnId
	inner join IcmPurchaseOrder po on dn.PoId = po.PoId
	where mr.record_status = 'A'
	and mr.event_seq = 11
	and po.Closed = 0
	and mrd.record_status <> 'D'
	group by mr.bgm_proj_oper_seq, mr.bgm_source_seq
) mrpo on o.bgm_proj_oper_seq = mrpo.bgm_proj_oper_seq
	and oe.bgm_source_seq = mrpo.bgm_source_seq

left join (select mr.bgm_proj_oper_seq, mr.bgm_source_seq
	, sum(coalesce(mrd.amount, 0)) as amount
	from fim_money_request_dtls mrd
	inner join fim_money_requests mr on mrd.money_req_seq = mr.money_req_seq
	where mr.record_status = 'A'
	and mrd.record_status <> 'D'
	group by mr.bgm_proj_oper_seq, mr.bgm_source_seq
) mr on o.bgm_proj_oper_seq = mr.bgm_proj_oper_seq
	and oe.bgm_source_seq = mr.bgm_source_seq

left join (select mr.bgm_proj_oper_seq, p.bgm_source_seq
	, sum(coalesce(mrd.amount, 0)) as amount
	from fim_payments p
	inner join fim_money_request_dtls mrd on p.money_req_seq = mrd.money_req_seq
	inner join fim_money_requests mr on p.money_req_seq = mr.money_req_seq
	where p.record_status = 'A'
	and mrd.record_status <> 'D'
	group by mr.bgm_proj_oper_seq, p.bgm_source_seq
) p on o.bgm_proj_oper_seq = p.bgm_proj_oper_seq
	and oe.bgm_source_seq = p.bgm_source_seq

left join (select mr.bgm_proj_oper_seq, r.bgm_source_seq
	, sum(coalesce(r.amount, 0)) as amount
	from fim_receives r
	inner join fim_money_requests mr on r.money_req_seq = mr.money_req_seq
	where r.record_status not in ('D', 'C')
	group by mr.bgm_proj_oper_seq, r.bgm_source_seq
) r on o.bgm_proj_oper_seq = r.bgm_proj_oper_seq
	and oe.bgm_source_seq = r.bgm_source_seq

left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq
	, sum(coalesce(rpt.cal_amount ,0)) as amount
	from bgm_project_operation_expense oe
	left join bgm_project_operation_report rpt on oe.bgm_proj_oper_exp_seq = rpt.bgm_proj_oper_exp_seq
	left join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
	where oe.record_status not like 'D'
	and rpt.record_status not like 'D'
	and rpt.rpt_type = 1
	and rpt.cal_amount < 0
	group by oe.bgm_proj_oper_seq, pe.bgm_source_seq
	) rpt7 on o.bgm_proj_oper_seq = rpt7.bgm_proj_oper_seq
	and oe.bgm_source_seq = rpt7.bgm_source_seq
left join fim_mas_record_statuses rs on o.record_status = rs.record_status
where o.record_status in ('A', 'C') and o.bgm_proj_seq = @bgm_proj_seq
";
                #region Report for TreeList
//                string sql = @"(select concat('/', o.bgm_proj_oper_seq) as child_seq
//                    , null as parent_seq
//                    , o.bgm_proj_oper_name as name
//                    , o.bgm_proj_oper_code as code
//
//                    , coalesce(oe.amount, 0) as operation_amount
//                    , coalesce(rpt1.cal_amount, 0) as transfer_amount
//                    , coalesce(rpt2.cal_amount, 0) as reserve_amount
//                    , coalesce(rpt3.cal_amount, 0) as moneyreq_amount
//                    , coalesce(rpt4.cal_amount, 0) as pay_amount
//                    , coalesce(rpt5.cal_amount, 0) as return_amount
//                    , coalesce(rpt6.cal_amount, 0) as remain_amount
//                    , coalesce(rpt7.cal_amount, 0) as cancel_amount
//                    , rs.record_status_name, o.record_status
//                    from bgm_project_operation o
//                    left join (select oe.bgm_proj_oper_seq
//	                    , sum(coalesce(rpt.cal_amount ,0)) as amount
//	                    from bgm_project_operation_expense oe
//                        left join bgm_project_operation_report rpt on oe.bgm_proj_oper_exp_seq = rpt.bgm_proj_oper_exp_seq
//	                    where oe.record_status not like 'D'
//                        and rpt.record_status not like 'D'
//	                    and rpt.rpt_type = 1
//                        and rpt.cal_amount > 0
//	                    group by oe.bgm_proj_oper_seq
//	                    ) oe on o.bgm_proj_oper_seq = oe.bgm_proj_oper_seq
//                    left join (select oe.bgm_proj_oper_seq
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (2,3)
//	                    group by oe.bgm_proj_oper_seq
//	                    ) rpt1 on o.bgm_proj_oper_seq = rpt1.bgm_proj_oper_seq
//                    left join (select oe.bgm_proj_oper_seq
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (4,5)
//	                    group by oe.bgm_proj_oper_seq
//	                    ) rpt2 on o.bgm_proj_oper_seq = rpt2.bgm_proj_oper_seq
//                    left join (select oe.bgm_proj_oper_seq
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (6)
//	                    group by oe.bgm_proj_oper_seq
//	                    ) rpt3 on o.bgm_proj_oper_seq = rpt3.bgm_proj_oper_seq
//                    left join (select oe.bgm_proj_oper_seq
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (7)
//	                    group by oe.bgm_proj_oper_seq
//	                    ) rpt4 on o.bgm_proj_oper_seq = rpt4.bgm_proj_oper_seq
//                    left join (select oe.bgm_proj_oper_seq
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (8)
//	                    group by oe.bgm_proj_oper_seq
//	                    ) rpt5 on o.bgm_proj_oper_seq = rpt5.bgm_proj_oper_seq
//                    left join (select oe.bgm_proj_oper_seq
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    where rpt.record_status not like 'D'
//	                    group by oe.bgm_proj_oper_seq
//	                    ) rpt6 on o.bgm_proj_oper_seq = rpt6.bgm_proj_oper_seq
//                    left join (select oe.bgm_proj_oper_seq
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (1)
//                        and rpt.cal_amount < 0
//	                    group by oe.bgm_proj_oper_seq
//	                    ) rpt7 on o.bgm_proj_oper_seq = rpt7.bgm_proj_oper_seq
//                    left join fim_mas_record_statuses rs on o.record_status = rs.record_status
//
//                    where o.record_status in ('A', 'C')
//                    and o.bgm_proj_seq = @bgm_proj_seq
//                    )
//                    union ALL
//                    (
//                    select concat('/', o.bgm_proj_oper_seq, '/', oe.bgm_source_seq) as child_seq
//                    , concat('/', o.bgm_proj_oper_seq) as parent_seq
//                    , s.bgm_source_name as name
//                    , null as code
//
//                    , coalesce(oe.amount, 0) as operation_amount
//                    , coalesce(rpt1.cal_amount, 0) as transfer_amount
//                    , coalesce(rpt2.cal_amount, 0) as reserve_amount
//                    , coalesce(rpt3.cal_amount, 0) as moneyreq_amount
//                    , coalesce(rpt4.cal_amount, 0) as pay_amount
//                    , coalesce(rpt5.cal_amount, 0) as return_amount
//                    , coalesce(rpt6.cal_amount, 0) as remain_amount
//                    , coalesce(rpt7.cal_amount, 0) as cancel_amount
//                    , null as record_status_name, null as record_status
//                    from bgm_project_operation o
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq
//	                    , sum(coalesce(rpt.cal_amount ,0)) as amount
//	                    from bgm_project_operation_expense oe
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//                        left join bgm_project_operation_report rpt on oe.bgm_proj_oper_exp_seq = rpt.bgm_proj_oper_exp_seq
//	                    where oe.record_status not like 'D'
//                        and rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (1)
//                        and rpt.cal_amount > 0
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq
//	                    ) oe on o.bgm_proj_oper_seq = oe.bgm_proj_oper_seq
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (2,3)
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq
//	                    ) rpt1 on o.bgm_proj_oper_seq = rpt1.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt1.bgm_source_seq
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (4,5)
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq
//	                    ) rpt2 on o.bgm_proj_oper_seq = rpt2.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt2.bgm_source_seq
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (6)
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq
//	                    ) rpt3 on o.bgm_proj_oper_seq = rpt3.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt3.bgm_source_seq
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (7)
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq
//	                    ) rpt4 on o.bgm_proj_oper_seq = rpt4.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt4.bgm_source_seq
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (8)
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq
//	                    ) rpt5 on o.bgm_proj_oper_seq = rpt5.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt5.bgm_source_seq
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq
//	                    ) rpt6 on o.bgm_proj_oper_seq = rpt6.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt6.bgm_source_seq
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (1)
//                        and rpt.cal_amount < 0
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq
//	                    ) rpt7 on o.bgm_proj_oper_seq = rpt7.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt7.bgm_source_seq
//                    left join bgm_mas_source s on oe.bgm_source_seq = s.bgm_source_seq
//                    left join fim_mas_record_statuses rs on o.record_status = rs.record_status
//
//                    where o.record_status in ('A', 'C')
//                    and o.bgm_proj_seq = @bgm_proj_seq
//                    )
//                    union ALL
//                    (
//                    select concat('/', o.bgm_proj_oper_seq, '/', oe.bgm_source_seq, '/', oe.bgm_exp_grp_seq) as child_seq
//                    , concat('/', o.bgm_proj_oper_seq, '/', oe.bgm_source_seq) as parent_seq
//                    , eg.bgm_exp_grp_name as name
//                    , null as code
//
//                    , coalesce(oe.amount, 0) as operation_amount
//                    , coalesce(rpt1.cal_amount, 0) as transfer_amount
//                    , coalesce(rpt2.cal_amount, 0) as reserve_amount
//                    , coalesce(rpt3.cal_amount, 0) as moneyreq_amount
//                    , coalesce(rpt4.cal_amount, 0) as pay_amount
//                    , coalesce(rpt5.cal_amount, 0) as return_amount
//                    , coalesce(rpt6.cal_amount, 0) as remain_amount
//                    , coalesce(rpt7.cal_amount, 0) as cancel_amount
//                    , null as record_status_name, null as record_status
//                    from bgm_project_operation o
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//	                    , sum(coalesce(rpt.cal_amount ,0)) as amount
//	                    from bgm_project_operation_expense oe
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//                        left join bgm_project_operation_report rpt on oe.bgm_proj_oper_exp_seq = rpt.bgm_proj_oper_exp_seq
//	                    where oe.record_status not like 'D'
//                        and rpt.record_status not like 'D'
//                        and rpt.rpt_type in (1)
//                        and rpt.cal_amount > 0
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//	                    ) oe on o.bgm_proj_oper_seq = oe.bgm_proj_oper_seq
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (2,3)
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//	                    ) rpt1 on o.bgm_proj_oper_seq = rpt1.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt1.bgm_source_seq
//                        and oe.bgm_exp_grp_seq = rpt1.bgm_exp_grp_seq
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (4,5)
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//	                    ) rpt2 on o.bgm_proj_oper_seq = rpt2.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt2.bgm_source_seq
//                        and oe.bgm_exp_grp_seq = rpt2.bgm_exp_grp_seq
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (6)
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//	                    ) rpt3 on o.bgm_proj_oper_seq = rpt3.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt3.bgm_source_seq
//                        and oe.bgm_exp_grp_seq = rpt3.bgm_exp_grp_seq
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (7)
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//	                    ) rpt4 on o.bgm_proj_oper_seq = rpt4.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt4.bgm_source_seq
//                        and oe.bgm_exp_grp_seq = rpt4.bgm_exp_grp_seq
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (8)
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//	                    ) rpt5 on o.bgm_proj_oper_seq = rpt5.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt5.bgm_source_seq
//                        and oe.bgm_exp_grp_seq = rpt5.bgm_exp_grp_seq
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//	                    ) rpt6 on o.bgm_proj_oper_seq = rpt6.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt6.bgm_source_seq
//                        and oe.bgm_exp_grp_seq = rpt6.bgm_exp_grp_seq
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (1)
//                        and rpt.cal_amount < 0
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//	                    ) rpt7 on o.bgm_proj_oper_seq = rpt7.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt7.bgm_source_seq
//                        and oe.bgm_exp_grp_seq = rpt7.bgm_exp_grp_seq
//                    left join bgm_mas_expense_group eg on oe.bgm_exp_grp_seq = eg.bgm_exp_grp_seq
//                    left join fim_mas_record_statuses rs on o.record_status = rs.record_status
//
//                    where o.record_status in ('A', 'C')
//                    and o.bgm_proj_seq = @bgm_proj_seq
//                    )
//                    union ALL
//                    (
//                    select concat('/', o.bgm_proj_oper_seq, '/', oe.bgm_source_seq, '/', oe.bgm_exp_grp_seq, '/', oe.bgm_exp_seq3) as child_seq
//                    , concat('/', o.bgm_proj_oper_seq, '/', oe.bgm_source_seq, '/', oe.bgm_exp_grp_seq) as parent_seq
//                    , oe.bgm_exp_name as name
//                    , null as code
//
//                    , coalesce(oe.amount, 0) as operation_amount
//                    , coalesce(rpt1.cal_amount, 0) as transfer_amount
//                    , coalesce(rpt2.cal_amount, 0) as reserve_amount
//                    , coalesce(rpt3.cal_amount, 0) as moneyreq_amount
//                    , coalesce(rpt4.cal_amount, 0) as pay_amount
//                    , coalesce(rpt5.cal_amount, 0) as return_amount
//                    , coalesce(rpt6.cal_amount, 0) as remain_amount
//                    , coalesce(rpt7.cal_amount, 0) as cancel_amount
//                    , null as record_status_name, null as record_status
//                    from bgm_project_operation o
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e1.bgm_exp_grp_seq
//	                    , e3.bgm_exp_seq as bgm_exp_seq3
//
//	                    , e3.bgm_exp_name
//	                    , sum(coalesce(rpt.cal_amount ,0)) as amount
//	                    from bgm_project_operation_expense oe
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e1 on pe.bgm_exp_seq = e1.bgm_exp_seq
//	                    left join bgm_mas_expense e2 on e1.upper_bgm_exp_seq = e2.bgm_exp_seq
//	                    left join bgm_mas_expense e3 on e2.upper_bgm_exp_seq = e3.bgm_exp_seq
//                        left join bgm_project_operation_report rpt on oe.bgm_proj_oper_exp_seq = rpt.bgm_proj_oper_exp_seq
//	                    where oe.record_status not like 'D'
//                        and rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (1)
//                        and rpt.cal_amount > 0
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e1.bgm_exp_grp_seq, e3.bgm_exp_seq, e3.bgm_exp_name
//	                    ) oe on o.bgm_proj_oper_seq = oe.bgm_proj_oper_seq
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq as bgm_exp_seq3
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    left join bgm_mas_expense e2 on e.upper_bgm_exp_seq = e2.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (2,3)
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq
//	                    ) rpt1 on o.bgm_proj_oper_seq = rpt1.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt1.bgm_source_seq
//	                    and oe.bgm_exp_seq3 = rpt1.bgm_exp_seq3
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq as bgm_exp_seq3
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    left join bgm_mas_expense e2 on e.upper_bgm_exp_seq = e2.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (4,5)
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq
//	                    ) rpt2 on o.bgm_proj_oper_seq = rpt2.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt2.bgm_source_seq
//	                    and oe.bgm_exp_seq3 = rpt2.bgm_exp_seq3
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq as bgm_exp_seq3
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    left join bgm_mas_expense e2 on e.upper_bgm_exp_seq = e2.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (6)
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq
//	                    ) rpt3 on o.bgm_proj_oper_seq = rpt3.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt3.bgm_source_seq
//	                    and oe.bgm_exp_seq3 = rpt3.bgm_exp_seq3
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq as bgm_exp_seq3
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    left join bgm_mas_expense e2 on e.upper_bgm_exp_seq = e2.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (7)
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq
//	                    ) rpt4 on o.bgm_proj_oper_seq = rpt4.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt4.bgm_source_seq
//	                    and oe.bgm_exp_seq3 = rpt4.bgm_exp_seq3
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq as bgm_exp_seq3
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    left join bgm_mas_expense e2 on e.upper_bgm_exp_seq = e2.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (8)
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq
//	                    ) rpt5 on o.bgm_proj_oper_seq = rpt5.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt5.bgm_source_seq
//	                    and oe.bgm_exp_seq3 = rpt5.bgm_exp_seq3
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq as bgm_exp_seq3
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    left join bgm_mas_expense e2 on e.upper_bgm_exp_seq = e2.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq
//	                    ) rpt6 on o.bgm_proj_oper_seq = rpt6.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt6.bgm_source_seq
//	                    and oe.bgm_exp_seq3 = rpt6.bgm_exp_seq3
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq as bgm_exp_seq3
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    left join bgm_mas_expense e2 on e.upper_bgm_exp_seq = e2.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (1)
//                        and rpt.cal_amount < 0
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq
//	                    ) rpt7 on o.bgm_proj_oper_seq = rpt7.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt7.bgm_source_seq
//	                    and oe.bgm_exp_seq3 = rpt7.bgm_exp_seq3
//                    left join fim_mas_record_statuses rs on o.record_status = rs.record_status
//
//                    where o.record_status in ('A', 'C')
//                    and o.bgm_proj_seq = @bgm_proj_seq
//                    )
//                    union ALL
//                    (
//                    select concat('/', o.bgm_proj_oper_seq, '/', oe.bgm_source_seq, '/', oe.bgm_exp_grp_seq, '/', oe.bgm_exp_seq3, '/', oe.bgm_exp_seq2) as child_seq
//                    , concat('/', o.bgm_proj_oper_seq, '/', oe.bgm_source_seq, '/', oe.bgm_exp_grp_seq, '/', oe.bgm_exp_seq3) as parent_seq
//                    , oe.bgm_exp_name as name
//                    , null as code
//
//                    , coalesce(oe.amount, 0) as operation_amount
//                    , coalesce(rpt1.cal_amount, 0) as transfer_amount
//                    , coalesce(rpt2.cal_amount, 0) as reserve_amount
//                    , coalesce(rpt3.cal_amount, 0) as moneyreq_amount
//                    , coalesce(rpt4.cal_amount, 0) as pay_amount
//                    , coalesce(rpt5.cal_amount, 0) as return_amount
//                    , coalesce(rpt6.cal_amount, 0) as remain_amount
//                    , coalesce(rpt7.cal_amount, 0) as cancel_amount
//                    , null as record_status_name, null as record_status
//                    from bgm_project_operation o
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e1.bgm_exp_grp_seq
//
//	                    , e2.bgm_exp_seq as bgm_exp_seq2
//	                    , e2.upper_bgm_exp_seq as bgm_exp_seq3
//
//	                    , e2.bgm_exp_name
//	                    , sum(coalesce(rpt.cal_amount ,0)) as amount
//	                    from bgm_project_operation_expense oe
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e1 on pe.bgm_exp_seq = e1.bgm_exp_seq
//	                    left join bgm_mas_expense e2 on e1.upper_bgm_exp_seq = e2.bgm_exp_seq
//                        left join bgm_project_operation_report rpt on oe.bgm_proj_oper_exp_seq = rpt.bgm_proj_oper_exp_seq
//	                    where oe.record_status not like 'D'
//                        and rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (1)
//                        and rpt.cal_amount > 0
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e1.bgm_exp_grp_seq, e2.bgm_exp_seq, e2.upper_bgm_exp_seq, e2.bgm_exp_name
//	                    ) oe on o.bgm_proj_oper_seq = oe.bgm_proj_oper_seq
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq as bgm_exp_seq2
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (2,3)
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq
//	                    ) rpt1 on o.bgm_proj_oper_seq = rpt1.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt1.bgm_source_seq
//	                    and oe.bgm_exp_seq2 = rpt1.bgm_exp_seq2
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq as bgm_exp_seq2
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (4,5)
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq
//	                    ) rpt2 on o.bgm_proj_oper_seq = rpt2.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt2.bgm_source_seq
//	                    and oe.bgm_exp_seq2 = rpt2.bgm_exp_seq2
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq as bgm_exp_seq2
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (6)
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq
//	                    ) rpt3 on o.bgm_proj_oper_seq = rpt3.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt3.bgm_source_seq
//	                    and oe.bgm_exp_seq2 = rpt3.bgm_exp_seq2
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq as bgm_exp_seq2
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (7)
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq
//	                    ) rpt4 on o.bgm_proj_oper_seq = rpt4.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt4.bgm_source_seq
//	                    and oe.bgm_exp_seq2 = rpt4.bgm_exp_seq2
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq as bgm_exp_seq2
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (8)
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq
//	                    ) rpt5 on o.bgm_proj_oper_seq = rpt5.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt5.bgm_source_seq
//	                    and oe.bgm_exp_seq2 = rpt5.bgm_exp_seq2
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq as bgm_exp_seq2
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq
//	                    ) rpt6 on o.bgm_proj_oper_seq = rpt6.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt6.bgm_source_seq
//	                    and oe.bgm_exp_seq2 = rpt6.bgm_exp_seq2
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq as bgm_exp_seq2
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (1)
//                        and rpt.cal_amount < 0
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq
//	                    ) rpt7 on o.bgm_proj_oper_seq = rpt7.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt7.bgm_source_seq
//	                    and oe.bgm_exp_seq2 = rpt7.bgm_exp_seq2
//                    left join fim_mas_record_statuses rs on o.record_status = rs.record_status
//
//                    where o.record_status in ('A', 'C')
//                    and o.bgm_proj_seq = @bgm_proj_seq
//                    )
//                    union ALL
//                    (
//                    select concat('/', o.bgm_proj_oper_seq, '/', oe.bgm_source_seq, '/', oe.bgm_exp_grp_seq, '/', oe.bgm_exp_seq3, '/', oe.bgm_exp_seq2, '/', oe.bgm_exp_seq1) as child_seq
//                    , concat('/', o.bgm_proj_oper_seq, '/', oe.bgm_source_seq, '/', oe.bgm_exp_grp_seq, '/', oe.bgm_exp_seq3, '/', oe.bgm_exp_seq2) as parent_seq
//                    , oe.bgm_exp_name as name
//                    , null as code
//
//                    , coalesce(oe.amount, 0) as operation_amount
//                    , coalesce(rpt1.cal_amount, 0) as transfer_amount
//                    , coalesce(rpt2.cal_amount, 0) as reserve_amount
//                    , coalesce(rpt3.cal_amount, 0) as moneyreq_amount
//                    , coalesce(rpt4.cal_amount, 0) as pay_amount
//                    , coalesce(rpt5.cal_amount, 0) as return_amount
//                    , coalesce(rpt6.cal_amount, 0) as remain_amount
//                    , coalesce(rpt7.cal_amount, 0) as cancel_amount
//                    , null as record_status_name, null as record_status
//                    from bgm_project_operation o
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e1.bgm_exp_grp_seq
//	                    , e1.bgm_exp_seq as bgm_exp_seq1
//	                    , e2.bgm_exp_seq as bgm_exp_seq2
//	                    , e2.upper_bgm_exp_seq as bgm_exp_seq3
//
//	                    , e1.bgm_exp_name
//	                    , sum(coalesce(rpt.cal_amount ,0)) as amount
//	                    from bgm_project_operation_expense oe
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e1 on pe.bgm_exp_seq = e1.bgm_exp_seq
//	                    left join bgm_mas_expense e2 on e1.upper_bgm_exp_seq = e2.bgm_exp_seq
//                        left join bgm_project_operation_report rpt on oe.bgm_proj_oper_exp_seq = rpt.bgm_proj_oper_exp_seq
//	                    where oe.record_status not like 'D'
//                        and rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (1)
//                        and rpt.cal_amount > 0
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e1.bgm_exp_grp_seq, e1.bgm_exp_seq, e2.bgm_exp_seq, e2.upper_bgm_exp_seq, e1.bgm_exp_name
//	                    ) oe on o.bgm_proj_oper_seq = oe.bgm_proj_oper_seq
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq as bgm_exp_seq1
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (2,3)
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq
//	                    ) rpt1 on o.bgm_proj_oper_seq = rpt1.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt1.bgm_source_seq
//	                    and oe.bgm_exp_seq1 = rpt1.bgm_exp_seq1
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq as bgm_exp_seq1
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (4,5)
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq
//	                    ) rpt2 on o.bgm_proj_oper_seq = rpt2.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt2.bgm_source_seq
//	                    and oe.bgm_exp_seq1 = rpt2.bgm_exp_seq1
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq as bgm_exp_seq1
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (6)
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq
//	                    ) rpt3 on o.bgm_proj_oper_seq = rpt3.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt3.bgm_source_seq
//	                    and oe.bgm_exp_seq1 = rpt3.bgm_exp_seq1
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq as bgm_exp_seq1
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (7)
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq
//	                    ) rpt4 on o.bgm_proj_oper_seq = rpt4.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt4.bgm_source_seq
//	                    and oe.bgm_exp_seq1 = rpt4.bgm_exp_seq1
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq as bgm_exp_seq1
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (8)
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq
//	                    ) rpt5 on o.bgm_proj_oper_seq = rpt5.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt5.bgm_source_seq
//	                    and oe.bgm_exp_seq1 = rpt5.bgm_exp_seq1
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq as bgm_exp_seq1
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq
//	                    ) rpt6 on o.bgm_proj_oper_seq = rpt6.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt6.bgm_source_seq
//	                    and oe.bgm_exp_seq1 = rpt6.bgm_exp_seq1
//                    left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq as bgm_exp_seq1
//	                    , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//	                    from bgm_project_operation_report rpt
//	                    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//		                    
//	                    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//	                    left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//	                    where rpt.record_status not like 'D'
//	                    and rpt.rpt_type in (1)
//                        and rpt.cal_amount < 0
//	                    group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq
//	                    ) rpt7 on o.bgm_proj_oper_seq = rpt7.bgm_proj_oper_seq
//	                    and oe.bgm_source_seq = rpt7.bgm_source_seq
//	                    and oe.bgm_exp_seq1 = rpt7.bgm_exp_seq1
//                    left join fim_mas_record_statuses rs on o.record_status = rs.record_status
//
//                    where o.record_status in ('A', 'C')
//                    and o.bgm_proj_seq = @bgm_proj_seq
//                    )";
                #endregion
                
                DataTable dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public enum ProjectDataType
        {
            Project
            , Source
            , ExpenseGroup
            , Expense1
            , Expense2
            , Expense3
        }

        public DataTable GetProjectReportOnDemand(ProjectDataType type, string bgm_year, string job_site_seq
            , string bgm_proj_seq, string parent_seq)
        {
            string sql = "";
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_year", bgm_year);
                dbManager.AddParameters(2, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(3, "@bgm_proj_seq", bgm_proj_seq);
                dbManager.AddParameters(4, "@parent_seq", parent_seq);

                //switch (type)
                //{
                //    case ProjectDataType.Project:
                        sql = @"
select concat('/', pj.bgm_proj_seq) as child_seq
, 'Project' as type
, null as parent_seq
, pj.bgm_proj_code as code
, pj.bgm_proj_name as name
, coalesce(pje.amount, 0) as operation_amount
, coalesce(rpt1.amount, 0) as transfer_amount
, coalesce(rpt2.amount, 0) as reserve_amount
, coalesce(po.amount, 0) - coalesce(mrpo.amount, 0) as purchase_amount
, coalesce(mr.amount, 0) - coalesce(p.amount, 0) as moneyreq_amount
, coalesce(p.amount, 0) - coalesce(r.amount, 0) as pay_amount
, coalesce(r.amount, 0) as return_amount
, coalesce(rpt2.amount, 0)
	- (coalesce(po.amount, 0) - coalesce(mrpo.amount, 0))
	- (coalesce(mr.amount, 0) - coalesce(p.amount, 0))
	- (coalesce(p.amount, 0) - coalesce(r.amount, 0)) as reserve_remain_amount
, coalesce(pje.amount, 0) + coalesce(rpt1.amount, 0) - coalesce(rpt2.amount, 0) as remain_amount
--, rs.record_status_name, o.record_status


from bgm_project pj

left join (select pje.bgm_proj_seq
	, sum(coalesce(pje.total_amount, 0)) as amount
	from bgm_project_expense pje
	group by pje.bgm_proj_seq
) pje on pj.bgm_proj_seq = pje.bgm_proj_seq

left join (select pje.bgm_proj_seq
	, sum(coalesce(rpt.cal_amount, 0)) as amount
	from bgm_project_report rpt
	left join bgm_project_expense pje on rpt.bgm_proj_exp_seq = pje.bgm_proj_exp_seq
	where rpt.record_status <> 'D'
	and rpt.rpt_type in (2,3)
	group by pje.bgm_proj_seq
) rpt1 on pj.bgm_proj_seq = rpt1.bgm_proj_seq

left join (select pe.bgm_proj_seq
    , sum(coalesce(rpt.cal_amount,0)) as amount
    from bgm_project_operation_report rpt
    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
    where rpt.record_status not like 'D'
    and rpt.rpt_type in (1,2,3) -- ,8)
    group by pe.bgm_proj_seq
) rpt2 on pj.bgm_proj_seq = rpt2.bgm_proj_seq

left join (select pje.bgm_proj_seq
	, sum((COALESCE(quo.NetAmount, 0) / COALESCE(quo.Qty, 1)) * ppri.Qty) as amount
	from IcmPurchaseRequestItem pri
  inner join IcmPurchaseRequest pr on pri.PrId = pr.PrId
	inner join IcmBidderQuotation quo on pri.PrItemId = quo.PrItemId
	inner join IcmPurchaseOrder po on quo.BidderId = po.BidderId and po.RecordStatus = 'A'
	inner join (select ppri.PrItemId, ppri.BgSourceId, ppri.ProjOperExpId, sum(ppri.Qty) as Qty
		from IcmPrePurchaseRequestItem ppri
		group by ppri.PrItemId, ppri.BgSourceId, ppri.ProjOperExpId
	) ppri on pri.PrItemId = ppri.PrItemId
	inner join bgm_project_operation_expense ope on ppri.ProjOperExpId = ope.bgm_proj_oper_exp_seq
	inner join bgm_project_expense pje on ope.bgm_proj_exp_seq = pje.bgm_proj_exp_seq
	where po.RecordStatus = 'A'
	and po.Closed = 0
	and pri.RecordStatus <> 'D'
	group by pje.bgm_proj_seq
) po on pj.bgm_proj_seq = po.bgm_proj_seq

left join (select op.bgm_proj_seq
	, sum(coalesce(mrd.amount, 0)) as amount
	from fim_money_request_dtls mrd
	inner join fim_money_requests mr on mrd.money_req_seq = mr.money_req_seq
	inner join bgm_project_operation op on mr.bgm_proj_oper_seq = op.bgm_proj_oper_seq
	inner join IcmDeliveryNote dn on mr.icm_dn_seq = dn.DnId
	inner join IcmPurchaseOrder po on dn.PoId = po.PoId
	where mr.record_status = 'A'
	and mr.event_seq = 11
	and po.Closed = 0
	and mrd.record_status <> 'D'
	group by op.bgm_proj_seq
) mrpo on pj.bgm_proj_seq = mrpo.bgm_proj_seq

left join (select op.bgm_proj_seq
	, sum(coalesce(mrd.amount, 0)) as amount
	from fim_money_request_dtls mrd
	inner join fim_money_requests mr on mrd.money_req_seq = mr.money_req_seq
	inner join bgm_project_operation op on mr.bgm_proj_oper_seq = op.bgm_proj_oper_seq
	where mr.record_status = 'A'
	and mrd.record_status <> 'D'
	group by op.bgm_proj_seq
) mr on pj.bgm_proj_seq = mr.bgm_proj_seq

left join (select op.bgm_proj_seq
	, sum(coalesce(mrd.amount, 0)) as amount
	from fim_payments p
	inner join fim_money_request_dtls mrd on p.money_req_seq = mrd.money_req_seq
	inner join fim_money_requests mr on p.money_req_seq = mr.money_req_seq
	inner join bgm_project_operation op on mr.bgm_proj_oper_seq = op.bgm_proj_oper_seq
	where p.record_status = 'A'
	and mrd.record_status <> 'D'
	group by op.bgm_proj_seq
) p on pj.bgm_proj_seq = p.bgm_proj_seq

left join (select op.bgm_proj_seq
	, sum(coalesce(r.amount, 0)) as amount
	from fim_receives r
	inner join fim_money_requests mr on r.money_req_seq = mr.money_req_seq
	inner join bgm_project_operation op on mr.bgm_proj_oper_seq = op.bgm_proj_oper_seq
	where r.record_status not in ('D', 'C')
	group by op.bgm_proj_seq
) r on pj.bgm_proj_seq = r.bgm_proj_seq

where pj.record_status in ('A', 'C')"
+ (!string.IsNullOrEmpty(bgm_proj_seq) ? " and pj.bgm_proj_seq = @bgm_proj_seq" : "")
+ (!string.IsNullOrEmpty(bgm_year) ? " and pj.bgm_year = @bgm_year" : "")
+ (!string.IsNullOrEmpty(job_site_seq) ? " and pj.job_site_seq = @job_site_seq" : "")
//+ @" order by code, name
//";
//                        break;

//                    case ProjectDataType.Source :
//                        sql = @"
+ @"
union all

select concat('/', pj.bgm_proj_seq, '/', pje.bgm_source_seq) as child_seq
, 'Source' as type
, concat('/', pj.bgm_proj_seq) as parent_seq
, '' as code
, pje.bgm_source_name as name
, coalesce(pje.amount, 0) as operation_amount
, coalesce(rpt1.amount, 0) as transfer_amount
, coalesce(rpt2.amount, 0) as reserve_amount
, coalesce(po.amount, 0) - coalesce(mrpo.amount, 0) as purchase_amount
, coalesce(mr.amount, 0) - coalesce(p.amount, 0) as moneyreq_amount
, coalesce(p.amount, 0) - coalesce(r.amount, 0) as pay_amount
, coalesce(r.amount, 0) as return_amount
, coalesce(rpt2.amount, 0)
	- (coalesce(po.amount, 0) - coalesce(mrpo.amount, 0))
	- (coalesce(mr.amount, 0) - coalesce(p.amount, 0))
	- (coalesce(p.amount, 0) - coalesce(r.amount, 0)) as reserve_remain_amount
, coalesce(pje.amount, 0) + coalesce(rpt1.amount, 0) - coalesce(rpt2.amount, 0) as remain_amount

from bgm_project pj

left join (select pje.bgm_proj_seq, pje.bgm_source_seq, s.bgm_source_name
	, sum(coalesce(pje.total_amount, 0)) as amount
	from bgm_project_expense pje
	left join bgm_mas_source s on pje.bgm_source_seq = s.bgm_source_seq
	group by pje.bgm_proj_seq, pje.bgm_source_seq, s.bgm_source_name
) pje on pj.bgm_proj_seq = pje.bgm_proj_seq

left join (select pje.bgm_proj_seq, pje.bgm_source_seq
	, sum(coalesce(rpt.cal_amount, 0)) as amount
	from bgm_project_report rpt
	left join bgm_project_expense pje on rpt.bgm_proj_exp_seq = pje.bgm_proj_exp_seq
	where rpt.record_status <> 'D'
	and rpt.rpt_type in (2,3)
	group by pje.bgm_proj_seq, pje.bgm_source_seq
) rpt1 on pj.bgm_proj_seq = rpt1.bgm_proj_seq
	and pje.bgm_source_seq = rpt1.bgm_source_seq

left join (select pe.bgm_proj_seq, pe.bgm_source_seq
    , sum(coalesce(rpt.cal_amount,0)) as amount
    from bgm_project_operation_report rpt
    inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
    inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
    where rpt.record_status not like 'D'
    and rpt.rpt_type in (1,2,3) -- ,8)
    group by pe.bgm_proj_seq, pe.bgm_source_seq
) rpt2 on pj.bgm_proj_seq = rpt2.bgm_proj_seq
	and pje.bgm_source_seq = rpt2.bgm_source_seq

left join (select pje.bgm_proj_seq, pje.bgm_source_seq
	, sum((COALESCE(quo.NetAmount, 0) / COALESCE(quo.Qty, 1)) * ppri.Qty) as amount
	from IcmPurchaseRequestItem pri
    inner join IcmPurchaseRequest pr on pri.PrId = pr.PrId
	inner join IcmBidderQuotation quo on pri.PrItemId = quo.PrItemId
	inner join IcmPurchaseOrder po on quo.BidderId = po.BidderId and po.RecordStatus = 'A'
	inner join (select ppri.PrItemId, ppri.BgSourceId, ppri.ProjOperExpId, sum(ppri.Qty) as Qty
		from IcmPrePurchaseRequestItem ppri
		group by ppri.PrItemId, ppri.BgSourceId, ppri.ProjOperExpId
	) ppri on pri.PrItemId = ppri.PrItemId
	inner join bgm_project_operation_expense ope on ppri.ProjOperExpId = ope.bgm_proj_oper_exp_seq
	inner join bgm_project_expense pje on ope.bgm_proj_exp_seq = pje.bgm_proj_exp_seq
	where po.RecordStatus = 'A'
	and po.Closed = 0
	and pri.RecordStatus <> 'D'
	group by pje.bgm_proj_seq, pje.bgm_source_seq
) po on pj.bgm_proj_seq = po.bgm_proj_seq
	and pje.bgm_source_seq = po.bgm_source_seq

left join (select op.bgm_proj_seq, mr.bgm_source_seq
	, sum(coalesce(mrd.amount, 0)) as amount
	from fim_money_request_dtls mrd
	inner join fim_money_requests mr on mrd.money_req_seq = mr.money_req_seq
	inner join bgm_project_operation op on mr.bgm_proj_oper_seq = op.bgm_proj_oper_seq
	inner join IcmDeliveryNote dn on mr.icm_dn_seq = dn.DnId
	inner join IcmPurchaseOrder po on dn.PoId = po.PoId
	where mr.record_status = 'A'
	and mr.event_seq = 11
	and po.Closed = 0
	group by op.bgm_proj_seq, mr.bgm_source_seq
) mrpo on pj.bgm_proj_seq = mrpo.bgm_proj_seq
	and pje.bgm_source_seq = mrpo.bgm_source_seq

left join (select op.bgm_proj_seq, mr.bgm_source_seq
	, sum(coalesce(mrd.amount, 0)) as amount
	from fim_money_request_dtls mrd
	inner join fim_money_requests mr on mrd.money_req_seq = mr.money_req_seq
	inner join bgm_project_operation op on mr.bgm_proj_oper_seq = op.bgm_proj_oper_seq
	where mr.record_status = 'A'
	group by op.bgm_proj_seq, mr.bgm_source_seq
) mr on pj.bgm_proj_seq = mr.bgm_proj_seq
	and pje.bgm_source_seq = mr.bgm_source_seq

left join (select op.bgm_proj_seq, mr.bgm_source_seq
	, sum(coalesce(mrd.amount, 0)) as amount
	from fim_payments p
	inner join fim_money_request_dtls mrd on p.money_req_seq = mrd.money_req_seq
	inner join fim_money_requests mr on p.money_req_seq = mr.money_req_seq
	inner join bgm_project_operation op on mr.bgm_proj_oper_seq = op.bgm_proj_oper_seq
	where p.record_status = 'A'
	group by op.bgm_proj_seq, mr.bgm_source_seq
) p on pj.bgm_proj_seq = p.bgm_proj_seq
	and pje.bgm_source_seq = p.bgm_source_seq

left join (select op.bgm_proj_seq, r.bgm_source_seq
	, sum(coalesce(r.amount, 0)) as amount
	from fim_receives r
	inner join fim_money_requests mr on r.money_req_seq = mr.money_req_seq
	inner join bgm_project_operation op on mr.bgm_proj_oper_seq = op.bgm_proj_oper_seq
	where r.record_status not in ('D', 'C')
	group by op.bgm_proj_seq, r.bgm_source_seq
) r on pj.bgm_proj_seq = r.bgm_proj_seq
	and pje.bgm_source_seq = r.bgm_source_seq
where pj.record_status in ('A', 'C')"
+ (!string.IsNullOrEmpty(bgm_proj_seq) ? " and pj.bgm_proj_seq = @bgm_proj_seq" : "")
+ (!string.IsNullOrEmpty(bgm_year) ? " and pj.bgm_year = @bgm_year" : "")
+ (!string.IsNullOrEmpty(job_site_seq) ? " and pj.job_site_seq = @job_site_seq" : "")


+ " order by code, name";

                //        break;
                //    default:
                //        return null;
                //}

                #region Old
//                switch (type)
//                {
//                    case ProjectDataType.Project:
//                        sql = @"select concat('/', o.bgm_proj_seq) as child_seq
//                            , 'Project' as type
//	                        , null as parent_seq
//	                        , o.bgm_proj_name as name
//                            , o.bgm_proj_code as code
//
//	                        , coalesce(oe.bath_amount, 0) as operation_amount
//	                        , coalesce(rpt1.cal_amount, 0) as transfer_amount
//	                        , coalesce(rpt2.cal_amount, 0) as reserve_amount
//	                        , coalesce(rpt3.cal_amount, 0) as moneyreq_amount
//	                        , coalesce(rpt4.cal_amount, 0) + coalesce(rpt5.cal_amount, 0) as pay_amount
//	                        , coalesce(rpt5.cal_amount, 0) as return_amount
//                            , coalesce(rpt2.cal_amount, 0) + coalesce(rpt3.cal_amount, 0) + coalesce(rpt4.cal_amount, 0) + coalesce(rpt5.cal_amount, 0) + coalesce(rpt7.cal_amount, 0) as reserve_remain_amount
//	                        , coalesce(rpt6.cal_amount, 0) as remain_amount
//	                        , coalesce(rpt7.cal_amount, 0) as purchase_amount
//	                        , rs.record_status_name, o.record_status
//	                        from bgm_project o
//	                        left join (select bgm_proj_seq
//		                        , sum(coalesce(total_amount ,0)) as bath_amount
//		                        from bgm_project_expense
//		                        group by bgm_proj_seq
//		                        ) oe on o.bgm_proj_seq = oe.bgm_proj_seq
//	                        left join (select oe.bgm_proj_seq
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_report rpt
//		                        inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (2,3)
//		                        group by oe.bgm_proj_seq
//		                        ) rpt1 on o.bgm_proj_seq = rpt1.bgm_proj_seq
//	                        left join (select pe.bgm_proj_seq
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//                                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (1,2,3) -- ,8)
//		                        group by pe.bgm_proj_seq
//		                        ) rpt2 on o.bgm_proj_seq = rpt2.bgm_proj_seq
//	                        left join (select pe.bgm_proj_seq
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//                                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (6)
//		                        group by pe.bgm_proj_seq
//		                        ) rpt3 on o.bgm_proj_seq = rpt3.bgm_proj_seq
//	                        left join (select pe.bgm_proj_seq
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//                                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (4)
//		                        group by pe.bgm_proj_seq
//		                        ) rpt7 on o.bgm_proj_seq = rpt7.bgm_proj_seq
//	                        left join (select pe.bgm_proj_seq
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//                                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (7)
//		                        group by pe.bgm_proj_seq
//		                        ) rpt4 on o.bgm_proj_seq = rpt4.bgm_proj_seq
//	                        left join (select pe.bgm_proj_seq
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//                                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (8)
//		                        group by pe.bgm_proj_seq
//		                        ) rpt5 on o.bgm_proj_seq = rpt5.bgm_proj_seq
//	                        left join (select oe.bgm_proj_seq
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_report rpt
//		                        inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        group by oe.bgm_proj_seq
//		                        ) rpt6 on o.bgm_proj_seq = rpt6.bgm_proj_seq
//	                        left join fim_mas_record_statuses rs on o.record_status = rs.record_status
//
//	                        where o.record_status in ('A', 'C')"
//                            + (!string.IsNullOrEmpty(bgm_proj_seq) ? " and o.bgm_proj_seq = @bgm_proj_seq" : "")
//                            + (!string.IsNullOrEmpty(bgm_year) ? " and o.bgm_year = @bgm_year" : "")
//                            + (!string.IsNullOrEmpty(job_site_seq) ? " and o.job_site_seq = @job_site_seq" : "");
//                        break;
//                    case ProjectDataType.Source:
//                        sql = @"select * from (
//                            select concat('/', o.bgm_proj_seq, '/', oe.bgm_source_seq) as child_seq
//                            , 'Source' as type
//	                        , concat('/', o.bgm_proj_seq) as parent_seq
//	                        , s.bgm_source_name as name
//                            , null as code
//
//	                        , coalesce(oe.bath_amount, 0) as operation_amount
//	                        , coalesce(rpt1.cal_amount, 0) as transfer_amount
//	                        , coalesce(rpt2.cal_amount, 0) as reserve_amount
//	                        , coalesce(rpt3.cal_amount, 0) as moneyreq_amount
//	                        , coalesce(rpt4.cal_amount, 0) + coalesce(rpt5.cal_amount, 0) as pay_amount
//	                        , coalesce(rpt5.cal_amount, 0) as return_amount
//                            , coalesce(rpt2.cal_amount, 0) + coalesce(rpt3.cal_amount, 0) + coalesce(rpt4.cal_amount, 0) + coalesce(rpt5.cal_amount, 0) + coalesce(rpt7.cal_amount, 0) as reserve_remain_amount
//	                        , coalesce(rpt6.cal_amount, 0) as remain_amount
//	                        , coalesce(rpt7.cal_amount, 0) as purchase_amount
//	                        , null as record_status_name, null as record_status
//	                        from bgm_project o
//	                        left join (select oe.bgm_proj_seq, oe.bgm_source_seq
//		                        , sum(coalesce(oe.total_amount ,0)) as bath_amount
//		                        from bgm_project_expense oe
//		                        group by oe.bgm_proj_seq, oe.bgm_source_seq
//		                        ) oe on o.bgm_proj_seq = oe.bgm_proj_seq
//	                        left join (select oe.bgm_proj_seq, oe.bgm_source_seq
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_report rpt
//		                        inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
//			                
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (2,3)
//		                        group by oe.bgm_proj_seq, oe.bgm_source_seq
//		                        ) rpt1 on o.bgm_proj_seq = rpt1.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt1.bgm_source_seq
//	                        left join (select pe.bgm_proj_seq, pe.bgm_source_seq
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//                                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (1,2,3) -- ,8)
//		                        group by pe.bgm_proj_seq, pe.bgm_source_seq
//		                        ) rpt2 on o.bgm_proj_seq = rpt2.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt2.bgm_source_seq
//	                        left join (select pe.bgm_proj_seq, pe.bgm_source_seq
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//			                
//		                        inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (6)
//		                        group by pe.bgm_proj_seq, pe.bgm_source_seq
//		                        ) rpt3 on o.bgm_proj_seq = rpt3.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt3.bgm_source_seq
//	                        left join (select pe.bgm_proj_seq, pe.bgm_source_seq
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//			                
//		                        inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (4)
//		                        group by pe.bgm_proj_seq, pe.bgm_source_seq
//		                        ) rpt7 on o.bgm_proj_seq = rpt7.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt7.bgm_source_seq
//	                        left join (select pe.bgm_proj_seq, pe.bgm_source_seq
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//			                
//		                        inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (7)
//		                        group by pe.bgm_proj_seq, pe.bgm_source_seq
//		                        ) rpt4 on o.bgm_proj_seq = rpt4.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt4.bgm_source_seq
//	                        left join (select pe.bgm_proj_seq, pe.bgm_source_seq
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//			                
//		                        inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (8)
//		                        group by pe.bgm_proj_seq, pe.bgm_source_seq
//		                        ) rpt5 on o.bgm_proj_seq = rpt5.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt5.bgm_source_seq
//	                        left join (select oe.bgm_proj_seq, oe.bgm_source_seq
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_report rpt
//		                        inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
//			                
//		                        where rpt.record_status not like 'D'
//		                        group by oe.bgm_proj_seq, oe.bgm_source_seq
//		                        ) rpt6 on o.bgm_proj_seq = rpt6.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt6.bgm_source_seq
//	                        left join bgm_mas_source s on oe.bgm_source_seq = s.bgm_source_seq
//	                        left join fim_mas_record_statuses rs on o.record_status = rs.record_status
//
//	                        where o.record_status in ('A', 'C')
//                            ) t
//                            where parent_seq = @parent_seq";
//                        break;
//                    case ProjectDataType.ExpenseGroup:
//                        sql = @"select * from (
//                            select concat('/', o.bgm_proj_seq, '/', oe.bgm_source_seq, '/', oe.bgm_exp_grp_seq) as child_seq
//                            , 'ExpenseGroup' as type
//	                        , concat('/', o.bgm_proj_seq, '/', oe.bgm_source_seq) as parent_seq
//	                        , eg.bgm_exp_grp_name as name
//                            , null as code
//
//	                        , coalesce(oe.bath_amount, 0) as operation_amount
//	                        , coalesce(rpt1.cal_amount, 0) as transfer_amount
//	                        , coalesce(rpt2.cal_amount, 0) as reserve_amount
//	                        , coalesce(rpt3.cal_amount, 0) as moneyreq_amount
//	                        , coalesce(rpt4.cal_amount, 0) + coalesce(rpt5.cal_amount, 0) as pay_amount
//	                        , coalesce(rpt5.cal_amount, 0) as return_amount
//                            , coalesce(rpt2.cal_amount, 0) + coalesce(rpt3.cal_amount, 0) + coalesce(rpt4.cal_amount, 0) + coalesce(rpt5.cal_amount, 0) + coalesce(rpt7.cal_amount, 0) as reserve_remain_amount
//	                        , coalesce(rpt6.cal_amount, 0) as remain_amount
//	                        , coalesce(rpt7.cal_amount, 0) as purchase_amount
//	                        , null as record_status_name, null as record_status
//	                        from bgm_project o
//	                        left join (select oe.bgm_proj_seq, oe.bgm_source_seq, e.bgm_exp_grp_seq
//		                        , sum(coalesce(oe.total_amount ,0)) as bath_amount
//		                        from bgm_project_expense oe
//		                        left join bgm_mas_expense e on oe.bgm_exp_seq = e.bgm_exp_seq
//		                        group by oe.bgm_proj_seq, oe.bgm_source_seq, e.bgm_exp_grp_seq
//		                        ) oe on o.bgm_proj_seq = oe.bgm_proj_seq
//	                        left join (select oe.bgm_proj_seq, oe.bgm_source_seq, e.bgm_exp_grp_seq
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_report rpt
//		                        inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
//			                
//		                        left join bgm_mas_expense e on oe.bgm_exp_seq = e.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (2,3)
//		                        group by oe.bgm_proj_seq, oe.bgm_source_seq, e.bgm_exp_grp_seq
//		                        ) rpt1 on o.bgm_proj_seq = rpt1.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt1.bgm_source_seq
//                                and oe.bgm_exp_grp_seq = rpt1.bgm_exp_grp_seq
//	                        left join (select pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//                                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (1,2,3) -- ,8)
//		                        group by pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//		                        ) rpt2 on o.bgm_proj_seq = rpt2.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt2.bgm_source_seq
//                                and oe.bgm_exp_grp_seq = rpt2.bgm_exp_grp_seq
//	                        left join (select pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//			                
//		                        inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (6)
//		                        group by pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//		                        ) rpt3 on o.bgm_proj_seq = rpt3.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt3.bgm_source_seq
//                                and oe.bgm_exp_grp_seq = rpt3.bgm_exp_grp_seq
//	                        left join (select pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//			                
//		                        inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (4)
//		                        group by pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//		                        ) rpt7 on o.bgm_proj_seq = rpt7.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt7.bgm_source_seq
//                                and oe.bgm_exp_grp_seq = rpt7.bgm_exp_grp_seq
//	                        left join (select pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//			                
//		                        inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (7)
//		                        group by pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//		                        ) rpt4 on o.bgm_proj_seq = rpt4.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt4.bgm_source_seq
//                                and oe.bgm_exp_grp_seq = rpt4.bgm_exp_grp_seq
//	                        left join (select pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//			                
//		                        inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (8)
//		                        group by pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
//		                        ) rpt5 on o.bgm_proj_seq = rpt5.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt5.bgm_source_seq
//                                and oe.bgm_exp_grp_seq = rpt5.bgm_exp_grp_seq
//	                        left join (select oe.bgm_proj_seq, oe.bgm_source_seq, e.bgm_exp_grp_seq
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_report rpt
//		                        inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
//			                
//		                        left join bgm_mas_expense e on oe.bgm_exp_seq = e.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        group by oe.bgm_proj_seq, oe.bgm_source_seq, e.bgm_exp_grp_seq
//		                        ) rpt6 on o.bgm_proj_seq = rpt6.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt6.bgm_source_seq
//                                and oe.bgm_exp_grp_seq = rpt6.bgm_exp_grp_seq
//	                        left join bgm_mas_expense_group eg on oe.bgm_exp_grp_seq = eg.bgm_exp_grp_seq
//	                        left join fim_mas_record_statuses rs on o.record_status = rs.record_status
//
//	                        where o.record_status in ('A', 'C')
//                            ) t
//                            where parent_seq = @parent_seq";
//                        break;
//                    case ProjectDataType.Expense1:
//                        sql = @"select * from (
//                            select concat('/', o.bgm_proj_seq, '/', oe.bgm_source_seq, '/', oe.bgm_exp_grp_seq, '/', oe.bgm_exp_seq3) as child_seq
//                            , 'Expense1' as type
//	                        , concat('/', o.bgm_proj_seq, '/', oe.bgm_source_seq, '/', oe.bgm_exp_grp_seq) as parent_seq
//	                        , oe.bgm_exp_name as name
//                            , null as code
//
//	                        , coalesce(oe.bath_amount, 0) as operation_amount
//	                        , coalesce(rpt1.cal_amount, 0) as transfer_amount
//	                        , coalesce(rpt2.cal_amount, 0) as reserve_amount
//	                        , coalesce(rpt3.cal_amount, 0) as moneyreq_amount
//	                        , coalesce(rpt4.cal_amount, 0) + coalesce(rpt5.cal_amount, 0) as pay_amount
//	                        , coalesce(rpt5.cal_amount, 0) as return_amount
//                            , coalesce(rpt2.cal_amount, 0) + coalesce(rpt3.cal_amount, 0) + coalesce(rpt4.cal_amount, 0) + coalesce(rpt5.cal_amount, 0) + coalesce(rpt7.cal_amount, 0) as reserve_remain_amount
//	                        , coalesce(rpt6.cal_amount, 0) as remain_amount
//	                        , coalesce(rpt7.cal_amount, 0) as purchase_amount
//	                        , null as record_status_name, null as record_status
//	                        from bgm_project o
//	                        left join (select oe.bgm_proj_seq, oe.bgm_source_seq, e1.bgm_exp_grp_seq
//		                        , e3.bgm_exp_seq as bgm_exp_seq3
//
//		                        , e3.bgm_exp_name
//		                        , sum(coalesce(oe.total_amount ,0)) as bath_amount
//		                        from bgm_project_expense oe
//		                        left join bgm_mas_expense e1 on oe.bgm_exp_seq = e1.bgm_exp_seq
//		                        left join bgm_mas_expense e2 on e1.upper_bgm_exp_seq = e2.bgm_exp_seq
//		                        left join bgm_mas_expense e3 on e2.upper_bgm_exp_seq = e3.bgm_exp_seq
//		                        group by oe.bgm_proj_seq, oe.bgm_source_seq, e1.bgm_exp_grp_seq, e3.bgm_exp_seq, e3.bgm_exp_name
//		                        ) oe on o.bgm_proj_seq = oe.bgm_proj_seq
//	                        left join (select oe.bgm_proj_seq, oe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq as bgm_exp_seq3
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_report rpt
//		                        inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
//			                
//		                        left join bgm_mas_expense e on oe.bgm_exp_seq = e.bgm_exp_seq
//		                        left join bgm_mas_expense e2 on e.upper_bgm_exp_seq = e2.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (2,3)
//		                        group by oe.bgm_proj_seq, oe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq
//		                        ) rpt1 on o.bgm_proj_seq = rpt1.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt1.bgm_source_seq
//		                        and oe.bgm_exp_seq3 = rpt1.bgm_exp_seq3
//	                        left join (select pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq as bgm_exp_seq3
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//                                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//		                        left join bgm_mas_expense e2 on e.upper_bgm_exp_seq = e2.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (1,2,3) -- ,8)
//		                        group by pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq
//		                        ) rpt2 on o.bgm_proj_seq = rpt2.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt2.bgm_source_seq
//		                        and oe.bgm_exp_seq3 = rpt2.bgm_exp_seq3
//	                        left join (select pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq as bgm_exp_seq3
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//			                
//		                        inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//		                        left join bgm_mas_expense e2 on e.upper_bgm_exp_seq = e2.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (6)
//		                        group by pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq
//		                        ) rpt3 on o.bgm_proj_seq = rpt3.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt3.bgm_source_seq
//		                        and oe.bgm_exp_seq3 = rpt3.bgm_exp_seq3
//	                        left join (select pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq as bgm_exp_seq3
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//			                
//		                        inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//		                        left join bgm_mas_expense e2 on e.upper_bgm_exp_seq = e2.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (4)
//		                        group by pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq
//		                        ) rpt7 on o.bgm_proj_seq = rpt7.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt7.bgm_source_seq
//		                        and oe.bgm_exp_seq3 = rpt7.bgm_exp_seq3
//	                        left join (select pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq as bgm_exp_seq3
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//			                
//		                        inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//		                        left join bgm_mas_expense e2 on e.upper_bgm_exp_seq = e2.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (7)
//		                        group by pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq
//		                        ) rpt4 on o.bgm_proj_seq = rpt4.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt4.bgm_source_seq
//		                        and oe.bgm_exp_seq3 = rpt4.bgm_exp_seq3
//	                        left join (select pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq as bgm_exp_seq3
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//			                
//		                        inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//		                        left join bgm_mas_expense e2 on e.upper_bgm_exp_seq = e2.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (8)
//		                        group by pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq
//		                        ) rpt5 on o.bgm_proj_seq = rpt5.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt5.bgm_source_seq
//		                        and oe.bgm_exp_seq3 = rpt5.bgm_exp_seq3
//	                        left join (select oe.bgm_proj_seq, oe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq as bgm_exp_seq3
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_report rpt
//		                        inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
//			                
//		                        left join bgm_mas_expense e on oe.bgm_exp_seq = e.bgm_exp_seq
//		                        left join bgm_mas_expense e2 on e.upper_bgm_exp_seq = e2.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        group by oe.bgm_proj_seq, oe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq
//		                        ) rpt6 on o.bgm_proj_seq = rpt6.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt6.bgm_source_seq
//		                        and oe.bgm_exp_seq3 = rpt6.bgm_exp_seq3
//	                        left join fim_mas_record_statuses rs on o.record_status = rs.record_status
//
//	                        where o.record_status in ('A', 'C')
//                            ) t
//                            where parent_seq = @parent_seq";
//                        break;
//                    case ProjectDataType.Expense2:
//                        sql = @"select * from (
//                            select concat('/', o.bgm_proj_seq, '/', oe.bgm_source_seq, '/', oe.bgm_exp_grp_seq, '/', oe.bgm_exp_seq3, '/', oe.bgm_exp_seq2) as child_seq
//	                        , 'Expense2' as type
//                            , concat('/', o.bgm_proj_seq, '/', oe.bgm_source_seq, '/', oe.bgm_exp_grp_seq, '/', oe.bgm_exp_seq3) as parent_seq
//	                        , oe.bgm_exp_name as name
//                            , null as code
//
//	                        , coalesce(oe.bath_amount, 0) as operation_amount
//	                        , coalesce(rpt1.cal_amount, 0) as transfer_amount
//	                        , coalesce(rpt2.cal_amount, 0) as reserve_amount
//	                        , coalesce(rpt3.cal_amount, 0) as moneyreq_amount
//	                        , coalesce(rpt4.cal_amount, 0) + coalesce(rpt5.cal_amount, 0) as pay_amount
//	                        , coalesce(rpt5.cal_amount, 0) as return_amount
//                            , coalesce(rpt2.cal_amount, 0) + coalesce(rpt3.cal_amount, 0) + coalesce(rpt4.cal_amount, 0) + coalesce(rpt5.cal_amount, 0) + coalesce(rpt7.cal_amount, 0) as reserve_remain_amount
//	                        , coalesce(rpt6.cal_amount, 0) as remain_amount
//	                        , coalesce(rpt7.cal_amount, 0) as purchase_amount
//	                        , null as record_status_name, null as record_status
//	                        from bgm_project o
//	                        left join (select oe.bgm_proj_seq, oe.bgm_source_seq, e1.bgm_exp_grp_seq
//
//		                        , e2.bgm_exp_seq as bgm_exp_seq2
//		                        , e2.upper_bgm_exp_seq as bgm_exp_seq3
//
//		                        , e2.bgm_exp_name
//		                        , sum(coalesce(oe.total_amount ,0)) as bath_amount
//		                        from bgm_project_expense oe
//		                        left join bgm_mas_expense e1 on oe.bgm_exp_seq = e1.bgm_exp_seq
//		                        left join bgm_mas_expense e2 on e1.upper_bgm_exp_seq = e2.bgm_exp_seq
//		                        group by oe.bgm_proj_seq, oe.bgm_source_seq, e1.bgm_exp_grp_seq, e2.bgm_exp_seq, e2.upper_bgm_exp_seq, e2.bgm_exp_name
//		                        ) oe on o.bgm_proj_seq = oe.bgm_proj_seq
//	                        left join (select oe.bgm_proj_seq, oe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq as bgm_exp_seq2
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_report rpt
//		                        inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
//			                
//		                        left join bgm_mas_expense e on oe.bgm_exp_seq = e.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (2,3)
//		                        group by oe.bgm_proj_seq, oe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq
//		                        ) rpt1 on o.bgm_proj_seq = rpt1.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt1.bgm_source_seq
//		                        and oe.bgm_exp_seq2 = rpt1.bgm_exp_seq2
//	                        left join (select pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq as bgm_exp_seq2
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//                                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//                                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (1,2,3) -- ,8)
//		                        group by pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq
//		                        ) rpt2 on o.bgm_proj_seq = rpt2.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt2.bgm_source_seq
//		                        and oe.bgm_exp_seq2 = rpt2.bgm_exp_seq2
//	                        left join (select pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq as bgm_exp_seq2
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//			                
//		                        inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (6)
//		                        group by pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq
//		                        ) rpt3 on o.bgm_proj_seq = rpt3.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt3.bgm_source_seq
//		                        and oe.bgm_exp_seq2 = rpt3.bgm_exp_seq2
//	                        left join (select pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq as bgm_exp_seq2
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//			                
//		                        inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (4)
//		                        group by pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq
//		                        ) rpt7 on o.bgm_proj_seq = rpt7.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt7.bgm_source_seq
//		                        and oe.bgm_exp_seq2 = rpt7.bgm_exp_seq2
//	                        left join (select pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq as bgm_exp_seq2
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//			                
//		                        inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (7)
//		                        group by pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq
//		                        ) rpt4 on o.bgm_proj_seq = rpt4.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt4.bgm_source_seq
//		                        and oe.bgm_exp_seq2 = rpt4.bgm_exp_seq2
//	                        left join (select pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq as bgm_exp_seq2
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//			                
//		                        inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (8)
//		                        group by pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq
//		                        ) rpt5 on o.bgm_proj_seq = rpt5.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt5.bgm_source_seq
//		                        and oe.bgm_exp_seq2 = rpt5.bgm_exp_seq2
//	                        left join (select oe.bgm_proj_seq, oe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq as bgm_exp_seq2
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_report rpt
//		                        inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
//			                
//		                        left join bgm_mas_expense e on oe.bgm_exp_seq = e.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        group by oe.bgm_proj_seq, oe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq
//		                        ) rpt6 on o.bgm_proj_seq = rpt6.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt6.bgm_source_seq
//		                        and oe.bgm_exp_seq2 = rpt6.bgm_exp_seq2
//	                        left join fim_mas_record_statuses rs on o.record_status = rs.record_status
//
//	                        where o.record_status in ('A', 'C')
//                            ) t
//                            where parent_seq = @parent_seq";
//                        break;
//                    case ProjectDataType.Expense3:
//                        sql = @"select * from (
//                            select concat('/', o.bgm_proj_seq, '/', oe.bgm_source_seq, '/', oe.bgm_exp_grp_seq, '/', oe.bgm_exp_seq3, '/', oe.bgm_exp_seq2, '/', oe.bgm_exp_seq1) as child_seq
//	                        , 'Expense3' as type
//                            , concat('/', o.bgm_proj_seq, '/', oe.bgm_source_seq, '/', oe.bgm_exp_grp_seq, '/', oe.bgm_exp_seq3, '/', oe.bgm_exp_seq2) as parent_seq
//	                        , oe.bgm_exp_name as name
//                            , null as code
//
//	                        , coalesce(oe.bath_amount, 0) as operation_amount
//	                        , coalesce(rpt1.cal_amount, 0) as transfer_amount
//	                        , coalesce(rpt2.cal_amount, 0) as reserve_amount
//	                        , coalesce(rpt3.cal_amount, 0) as moneyreq_amount
//	                        , coalesce(rpt4.cal_amount, 0) + coalesce(rpt5.cal_amount, 0) as pay_amount
//	                        , coalesce(rpt5.cal_amount, 0) as return_amount
//                            , coalesce(rpt2.cal_amount, 0) + coalesce(rpt3.cal_amount, 0) + coalesce(rpt4.cal_amount, 0) + coalesce(rpt5.cal_amount, 0) + coalesce(rpt7.cal_amount, 0) as reserve_remain_amount
//	                        , coalesce(rpt6.cal_amount, 0) as remain_amount
//	                        , coalesce(rpt7.cal_amount, 0) as purchase_amount
//	                        , null as record_status_name, null as record_status
//	                        from bgm_project o
//	                        left join (select oe.bgm_proj_seq, oe.bgm_source_seq, e1.bgm_exp_grp_seq
//		                        , e1.bgm_exp_seq as bgm_exp_seq1
//		                        , e2.bgm_exp_seq as bgm_exp_seq2
//		                        , e2.upper_bgm_exp_seq as bgm_exp_seq3
//
//		                        , e1.bgm_exp_name
//		                        , sum(coalesce(oe.total_amount ,0)) as bath_amount
//		                        from bgm_project_expense oe
//		                        left join bgm_mas_expense e1 on oe.bgm_exp_seq = e1.bgm_exp_seq
//		                        left join bgm_mas_expense e2 on e1.upper_bgm_exp_seq = e2.bgm_exp_seq
//
//		                        group by oe.bgm_proj_seq, oe.bgm_source_seq, e1.bgm_exp_grp_seq, e1.bgm_exp_seq, e2.bgm_exp_seq, e2.upper_bgm_exp_seq, e1.bgm_exp_name
//		                        ) oe on o.bgm_proj_seq = oe.bgm_proj_seq
//	                        left join (select oe.bgm_proj_seq, oe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq as bgm_exp_seq1
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_report rpt
//		                        inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
//			                
//		                        left join bgm_mas_expense e on oe.bgm_exp_seq = e.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (2,3)
//		                        group by oe.bgm_proj_seq, oe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq
//		                        ) rpt1 on o.bgm_proj_seq = rpt1.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt1.bgm_source_seq
//		                        and oe.bgm_exp_seq1 = rpt1.bgm_exp_seq1
//	                        left join (select pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq as bgm_exp_seq1
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//                                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//                                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (1,2,3) -- ,8)
//		                        group by pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq
//		                        ) rpt2 on o.bgm_proj_seq = rpt2.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt2.bgm_source_seq
//		                        and oe.bgm_exp_seq1 = rpt2.bgm_exp_seq1
//	                        left join (select pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq as bgm_exp_seq1
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//			                
//		                        inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (6)
//		                        group by pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq
//		                        ) rpt3 on o.bgm_proj_seq = rpt3.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt3.bgm_source_seq
//		                        and oe.bgm_exp_seq1 = rpt3.bgm_exp_seq1
//	                        left join (select pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq as bgm_exp_seq1
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//			                
//		                        inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (4)
//		                        group by pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq
//		                        ) rpt7 on o.bgm_proj_seq = rpt7.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt7.bgm_source_seq
//		                        and oe.bgm_exp_seq1 = rpt7.bgm_exp_seq1
//	                        left join (select pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq as bgm_exp_seq1
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//			                
//		                        inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (7)
//		                        group by pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq
//		                        ) rpt4 on o.bgm_proj_seq = rpt4.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt4.bgm_source_seq
//		                        and oe.bgm_exp_seq1 = rpt4.bgm_exp_seq1
//	                        left join (select pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq as bgm_exp_seq1
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_operation_report rpt
//		                        inner join bgm_project_operation_expense oe on rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
//			                
//		                        inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
//		                        left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        and rpt.rpt_type in (8)
//		                        group by pe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq
//		                        ) rpt5 on o.bgm_proj_seq = rpt5.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt5.bgm_source_seq
//		                        and oe.bgm_exp_seq1 = rpt5.bgm_exp_seq1
//	                        left join (select oe.bgm_proj_seq, oe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq as bgm_exp_seq1
//		                        , sum(coalesce(rpt.cal_amount,0)) as cal_amount
//		                        from bgm_project_report rpt
//		                        inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
//			                
//		                        left join bgm_mas_expense e on oe.bgm_exp_seq = e.bgm_exp_seq
//		                        where rpt.record_status not like 'D'
//		                        group by oe.bgm_proj_seq, oe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq
//		                        ) rpt6 on o.bgm_proj_seq = rpt6.bgm_proj_seq
//		                        and oe.bgm_source_seq = rpt6.bgm_source_seq
//		                        and oe.bgm_exp_seq1 = rpt6.bgm_exp_seq1
//	                        left join fim_mas_record_statuses rs on o.record_status = rs.record_status
//
//	                        where o.record_status in ('A', 'C')
//                            ) t
//                            where parent_seq = @parent_seq";
//                        break;
//                }
                #endregion

                DataTable dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public IList<ProjectReportModel> GetProjectReportViewState(string bgm_year, string job_site_seq, string bgm_proj_seq)
        {
            string sql = "";
            ConvertNullable cn = new ConvertNullable();
            IList<ProjectReportModel> models = new List<ProjectReportModel>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_year", bgm_year);
                dbManager.AddParameters(2, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(3, "@bgm_proj_seq", bgm_proj_seq);

                sql = @"
select pj.bgm_proj_code, pj.bgm_proj_name

, exp1.bgm_exp_name as bgm_exp_name1
, exp2.bgm_exp_name as bgm_exp_name2
, exp3.bgm_exp_name as bgm_exp_name3
, expg.bgm_exp_grp_name as bgm_exp_grp_name
, s.bgm_source_name as bgm_source_name

, coalesce(rpt1.amount, 0) as project_amount
, coalesce(rpt2.amount, 0) as transfer_amount
, coalesce(rpt3.amount, 0) as operation_return_amount
, coalesce(rpt4.amount, 0) as operation_amount
, coalesce(rpt5.amount, 0) as icm_amount
, coalesce(rpt6.amount, 0) as money_req_amount
, coalesce(rpt7.amount, 0) as pay_amount
, coalesce(rpt8.amount, 0) as money_return_amount

from bgm_project pj
left join bgm_project_expense pje on pj.bgm_proj_seq = pje.bgm_proj_seq
left join bgm_mas_expense exp1 on pje.bgm_exp_seq = exp1.bgm_exp_seq
left join bgm_mas_expense exp2 on exp1.upper_bgm_exp_seq = exp2.bgm_exp_seq
left join bgm_mas_expense exp3 on exp2.upper_bgm_exp_seq = exp3.bgm_exp_seq
left join bgm_mas_expense_group expg on exp3.bgm_exp_grp_seq = expg.bgm_exp_grp_seq
left join bgm_mas_source s on pje.bgm_source_seq = s.bgm_source_seq

left join (select bgm_proj_exp_seq
	, sum(coalesce(cal_amount, 0)) as amount
	from bgm_project_report
	where record_status <> 'D'
	and rpt_type = 1
	group by bgm_proj_exp_seq
	) rpt1 on pje.bgm_proj_exp_seq = rpt1.bgm_proj_exp_seq

left join (select bgm_proj_exp_seq
	, sum(coalesce(cal_amount, 0)) as amount
	from bgm_project_report
	where record_status <> 'D'
	and rpt_type in (2,3)
	group by bgm_proj_exp_seq
	) rpt2 on pje.bgm_proj_exp_seq = rpt2.bgm_proj_exp_seq

left join (select bgm_proj_exp_seq
	, sum(coalesce(cal_amount, 0)) as amount
	from bgm_project_report
	where record_status <> 'D'
	and rpt_type in (8)
	group by bgm_proj_exp_seq
	) rpt3 on pje.bgm_proj_exp_seq = rpt3.bgm_proj_exp_seq

left join (select ope.bgm_proj_exp_seq
	, sum(coalesce(rpt.cal_amount, 0)) as amount
	from bgm_project_operation_report rpt
	left join bgm_project_operation_expense ope on ope.bgm_proj_oper_exp_seq = ope.bgm_proj_oper_exp_seq
	where rpt.record_status <> 'D'
	and ope.record_status <> 'D'
	and rpt.rpt_type in (1)
	group by ope.bgm_proj_exp_seq
	) rpt4 on pje.bgm_proj_exp_seq = rpt4.bgm_proj_exp_seq

left join (select ope.bgm_proj_exp_seq
	, sum(coalesce(rpt.cal_amount, 0)) as amount
	from bgm_project_operation_report rpt
	left join bgm_project_operation_expense ope on ope.bgm_proj_oper_exp_seq = ope.bgm_proj_oper_exp_seq
	where rpt.record_status <> 'D'
	and ope.record_status <> 'D'
	and rpt.rpt_type in (4,5)
	group by ope.bgm_proj_exp_seq
	) rpt5 on pje.bgm_proj_exp_seq = rpt5.bgm_proj_exp_seq

left join (select ope.bgm_proj_exp_seq
	, sum(coalesce(rpt.cal_amount, 0)) as amount
	from bgm_project_operation_report rpt
	left join bgm_project_operation_expense ope on ope.bgm_proj_oper_exp_seq = ope.bgm_proj_oper_exp_seq
	where rpt.record_status <> 'D'
	and ope.record_status <> 'D'
	and rpt.rpt_type in (6)
	group by ope.bgm_proj_exp_seq
	) rpt6 on pje.bgm_proj_exp_seq = rpt6.bgm_proj_exp_seq

left join (select ope.bgm_proj_exp_seq
	, sum(coalesce(rpt.cal_amount, 0)) as amount
	from bgm_project_operation_report rpt
	left join bgm_project_operation_expense ope on ope.bgm_proj_oper_exp_seq = ope.bgm_proj_oper_exp_seq
	where rpt.record_status <> 'D'
	and ope.record_status <> 'D'
	and rpt.rpt_type in (7)
	group by ope.bgm_proj_exp_seq
	) rpt7 on pje.bgm_proj_exp_seq = rpt7.bgm_proj_exp_seq

left join (select ope.bgm_proj_exp_seq
	, sum(coalesce(rpt.cal_amount, 0)) as amount
	from bgm_project_operation_report rpt
	left join bgm_project_operation_expense ope on ope.bgm_proj_oper_exp_seq = ope.bgm_proj_oper_exp_seq
	where rpt.record_status <> 'D'
	and ope.record_status <> 'D'
	and rpt.rpt_type in (8)
	group by ope.bgm_proj_exp_seq
	) rpt8 on pje.bgm_proj_exp_seq = rpt8.bgm_proj_exp_seq

where pj.record_status <> @delete_status
and pje.record_status <> @delete_status"

                    + (!string.IsNullOrEmpty(bgm_year) ? " and pj.bgm_year = @bgm_year" : "")
                    + (!string.IsNullOrEmpty(job_site_seq) ? " and pj.job_site_seq = @job_site_seq" : "")
                    + (!string.IsNullOrEmpty(bgm_proj_seq) ? " and pj.bgm_proj_seq = @bgm_proj_seq" : "");

                DataTable dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];

                foreach (DataRow dr in dt.Rows)
                {
                    ProjectReportModel model = new ProjectReportModel
                    {
                        bgm_proj_code = dr["bgm_proj_code"].ToString(),
                        bgm_proj_name = dr["bgm_proj_name"].ToString(),
                        bgm_source_name = dr["bgm_source_name"].ToString(),
                        bgm_exp_grp_name = dr["bgm_exp_grp_name"].ToString(),
                        icm_amount = cn.ConvertNullDouble(dr["icm_amount"]) ?? 0,
                        money_req_amount = cn.ConvertNullDouble(dr["money_req_amount"]) ?? 0,
                        money_return_amount = cn.ConvertNullDouble(dr["money_return_amount"]) ?? 0,
                        operation_amount = cn.ConvertNullDouble(dr["operation_amount"]) ?? 0,
                        operation_return_amount = cn.ConvertNullDouble(dr["operation_return_amount"]) ?? 0,
                        pay_amount = cn.ConvertNullDouble(dr["pay_amount"]) ?? 0,
                        project_amount = cn.ConvertNullDouble(dr["project_amount"]) ?? 0,
                        transfer_amount = cn.ConvertNullDouble(dr["transfer_amount"]) ?? 0,
                    };

                    models.Add(model);
                }

                return models;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetProjectReport(string bgm_year, string job_site_seq, string bgm_proj_seq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_year", bgm_year);
                dbManager.AddParameters(2, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(3, "@bgm_proj_seq", bgm_proj_seq);

                #region Report for TreeList
                string sql = @"(select concat('/', o.bgm_proj_seq) as child_seq
	                , null as parent_seq
	                , o.bgm_proj_name as name

	                , coalesce(oe.bath_amount, 0) as operation_amount
	                , coalesce(rpt1.cal_amount, 0) as transfer_amount
	                , coalesce(rpt2.cal_amount, 0) as reserve_amount
	                , coalesce(rpt3.cal_amount, 0) as moneyreq_amount
	                , coalesce(rpt4.cal_amount, 0) as pay_amount
	                , coalesce(rpt5.cal_amount, 0) as return_amount
	                , coalesce(rpt6.cal_amount, 0) as remain_amount
	                , rs.record_status_name, o.record_status
	                from bgm_project o
	                left join (select bgm_proj_seq
		                , sum(coalesce(total_amount ,0)) as bath_amount
		                from bgm_project_expense
		                group by bgm_proj_seq
		                ) oe on o.bgm_proj_seq = oe.bgm_proj_seq
	                left join (select oe.bgm_proj_seq
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (2,3)
		                group by oe.bgm_proj_seq
		                ) rpt1 on o.bgm_proj_seq = rpt1.bgm_proj_seq
	                left join (select oe.bgm_proj_seq
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (4,5)
		                group by oe.bgm_proj_seq
		                ) rpt2 on o.bgm_proj_seq = rpt2.bgm_proj_seq
	                left join (select oe.bgm_proj_seq
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (6)
		                group by oe.bgm_proj_seq
		                ) rpt3 on o.bgm_proj_seq = rpt3.bgm_proj_seq
	                left join (select oe.bgm_proj_seq
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (7)
		                group by oe.bgm_proj_seq
		                ) rpt4 on o.bgm_proj_seq = rpt4.bgm_proj_seq
	                left join (select oe.bgm_proj_seq
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (8)
		                group by oe.bgm_proj_seq
		                ) rpt5 on o.bgm_proj_seq = rpt5.bgm_proj_seq
	                left join (select oe.bgm_proj_seq
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
		                where rpt.record_status not like 'D'
		                group by oe.bgm_proj_seq
		                ) rpt6 on o.bgm_proj_seq = rpt6.bgm_proj_seq
	                left join fim_mas_record_statuses rs on o.record_status = rs.record_status

	                where o.record_status in ('A', 'C')"
	                + (!string.IsNullOrEmpty(bgm_proj_seq) ? " and o.bgm_proj_seq = @bgm_proj_seq" : "")
                    + (!string.IsNullOrEmpty(bgm_year) ? " and o.bgm_year = @bgm_year" : "")
                    + (!string.IsNullOrEmpty(job_site_seq) ? " and o.job_site_seq = @job_site_seq" : "")
	                + @")
	                union ALL
	                (
	                select concat('/', o.bgm_proj_seq, '/', oe.bgm_source_seq) as child_seq
	                , concat('/', o.bgm_proj_seq) as parent_seq
	                , s.bgm_source_name as name

	                , coalesce(oe.bath_amount, 0) as operation_amount
	                , coalesce(rpt1.cal_amount, 0) as transfer_amount
	                , coalesce(rpt2.cal_amount, 0) as reserve_amount
	                , coalesce(rpt3.cal_amount, 0) as moneyreq_amount
	                , coalesce(rpt4.cal_amount, 0) as pay_amount
	                , coalesce(rpt5.cal_amount, 0) as return_amount
	                , coalesce(rpt6.cal_amount, 0) as remain_amount
	                , null as record_status_name, null as record_status
	                from bgm_project o
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq
		                , sum(coalesce(oe.total_amount ,0)) as bath_amount
		                from bgm_project_expense oe
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                group by oe.bgm_proj_seq, pe.bgm_source_seq
		                ) oe on o.bgm_proj_seq = oe.bgm_proj_seq
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (2,3)
		                group by oe.bgm_proj_seq, pe.bgm_source_seq
		                ) rpt1 on o.bgm_proj_seq = rpt1.bgm_proj_seq
		                and oe.bgm_source_seq = rpt1.bgm_source_seq
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (4,5)
		                group by oe.bgm_proj_seq, pe.bgm_source_seq
		                ) rpt2 on o.bgm_proj_seq = rpt2.bgm_proj_seq
		                and oe.bgm_source_seq = rpt2.bgm_source_seq
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (6)
		                group by oe.bgm_proj_seq, pe.bgm_source_seq
		                ) rpt3 on o.bgm_proj_seq = rpt3.bgm_proj_seq
		                and oe.bgm_source_seq = rpt3.bgm_source_seq
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (7)
		                group by oe.bgm_proj_seq, pe.bgm_source_seq
		                ) rpt4 on o.bgm_proj_seq = rpt4.bgm_proj_seq
		                and oe.bgm_source_seq = rpt4.bgm_source_seq
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (8)
		                group by oe.bgm_proj_seq, pe.bgm_source_seq
		                ) rpt5 on o.bgm_proj_seq = rpt5.bgm_proj_seq
		                and oe.bgm_source_seq = rpt5.bgm_source_seq
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                where rpt.record_status not like 'D'
		                group by oe.bgm_proj_seq, pe.bgm_source_seq
		                ) rpt6 on o.bgm_proj_seq = rpt6.bgm_proj_seq
		                and oe.bgm_source_seq = rpt6.bgm_source_seq
	                left join bgm_mas_source s on oe.bgm_source_seq = s.bgm_source_seq
	                left join fim_mas_record_statuses rs on o.record_status = rs.record_status

	                where o.record_status in ('A', 'C')"
                    + (!string.IsNullOrEmpty(bgm_proj_seq) ? " and o.bgm_proj_seq = @bgm_proj_seq" : "")
                    + (!string.IsNullOrEmpty(bgm_year) ? " and o.bgm_year = @bgm_year" : "")
                    + (!string.IsNullOrEmpty(job_site_seq) ? " and o.job_site_seq = @job_site_seq" : "")
                    + @")
	                union ALL
	                (
	                select concat('/', o.bgm_proj_seq, '/', oe.bgm_source_seq, '/', oe.bgm_exp_grp_seq) as child_seq
	                , concat('/', o.bgm_proj_seq, '/', oe.bgm_source_seq) as parent_seq
	                , eg.bgm_exp_grp_name as name

	                , coalesce(oe.bath_amount, 0) as operation_amount
	                , coalesce(rpt1.cal_amount, 0) as transfer_amount
	                , coalesce(rpt2.cal_amount, 0) as reserve_amount
	                , coalesce(rpt3.cal_amount, 0) as moneyreq_amount
	                , coalesce(rpt4.cal_amount, 0) as pay_amount
	                , coalesce(rpt5.cal_amount, 0) as return_amount
	                , coalesce(rpt6.cal_amount, 0) as remain_amount
	                , null as record_status_name, null as record_status
	                from bgm_project o
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
		                , sum(coalesce(oe.total_amount ,0)) as bath_amount
		                from bgm_project_expense oe
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
		                ) oe on o.bgm_proj_seq = oe.bgm_proj_seq
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (2,3)
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
		                ) rpt1 on o.bgm_proj_seq = rpt1.bgm_proj_seq
		                and oe.bgm_source_seq = rpt1.bgm_source_seq
                        and oe.bgm_exp_grp_seq = rpt1.bgm_exp_grp_seq
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (4,5)
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
		                ) rpt2 on o.bgm_proj_seq = rpt2.bgm_proj_seq
		                and oe.bgm_source_seq = rpt2.bgm_source_seq
                        and oe.bgm_exp_grp_seq = rpt2.bgm_exp_grp_seq
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (6)
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
		                ) rpt3 on o.bgm_proj_seq = rpt3.bgm_proj_seq
		                and oe.bgm_source_seq = rpt3.bgm_source_seq
                        and oe.bgm_exp_grp_seq = rpt3.bgm_exp_grp_seq
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (7)
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
		                ) rpt4 on o.bgm_proj_seq = rpt4.bgm_proj_seq
		                and oe.bgm_source_seq = rpt4.bgm_source_seq
                        and oe.bgm_exp_grp_seq = rpt4.bgm_exp_grp_seq
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (8)
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
		                ) rpt5 on o.bgm_proj_seq = rpt5.bgm_proj_seq
		                and oe.bgm_source_seq = rpt5.bgm_source_seq
                        and oe.bgm_exp_grp_seq = rpt5.bgm_exp_grp_seq
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
		                where rpt.record_status not like 'D'
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
		                ) rpt6 on o.bgm_proj_seq = rpt6.bgm_proj_seq
		                and oe.bgm_source_seq = rpt6.bgm_source_seq
                        and oe.bgm_exp_grp_seq = rpt6.bgm_exp_grp_seq
	                left join bgm_mas_expense_group eg on oe.bgm_exp_grp_seq = eg.bgm_exp_grp_seq
	                left join fim_mas_record_statuses rs on o.record_status = rs.record_status

	                where o.record_status in ('A', 'C')"
                    + (!string.IsNullOrEmpty(bgm_proj_seq) ? " and o.bgm_proj_seq = @bgm_proj_seq" : "")
                    + (!string.IsNullOrEmpty(bgm_year) ? " and o.bgm_year = @bgm_year" : "")
                    + (!string.IsNullOrEmpty(job_site_seq) ? " and o.job_site_seq = @job_site_seq" : "")
                    + @")
	                union ALL
	                (
	                select concat('/', o.bgm_proj_seq, '/', oe.bgm_source_seq, '/', oe.bgm_exp_grp_seq, '/', oe.bgm_exp_seq3) as child_seq
	                , concat('/', o.bgm_proj_seq, '/', oe.bgm_source_seq, '/', oe.bgm_exp_grp_seq) as parent_seq
	                , oe.bgm_exp_name as name

	                , coalesce(oe.bath_amount, 0) as operation_amount
	                , coalesce(rpt1.cal_amount, 0) as transfer_amount
	                , coalesce(rpt2.cal_amount, 0) as reserve_amount
	                , coalesce(rpt3.cal_amount, 0) as moneyreq_amount
	                , coalesce(rpt4.cal_amount, 0) as pay_amount
	                , coalesce(rpt5.cal_amount, 0) as return_amount
	                , coalesce(rpt6.cal_amount, 0) as remain_amount
	                , null as record_status_name, null as record_status
	                from bgm_project o
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e1.bgm_exp_grp_seq
		                , e3.bgm_exp_seq as bgm_exp_seq3

		                , e3.bgm_exp_name
		                , sum(coalesce(oe.total_amount ,0)) as bath_amount
		                from bgm_project_expense oe
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e1 on pe.bgm_exp_seq = e1.bgm_exp_seq
		                left join bgm_mas_expense e2 on e1.upper_bgm_exp_seq = e2.bgm_exp_seq
		                left join bgm_mas_expense e3 on e2.upper_bgm_exp_seq = e3.bgm_exp_seq
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e1.bgm_exp_grp_seq, e3.bgm_exp_seq, e3.bgm_exp_name
		                ) oe on o.bgm_proj_seq = oe.bgm_proj_seq
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq as bgm_exp_seq3
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
		                left join bgm_mas_expense e2 on e.upper_bgm_exp_seq = e2.bgm_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (2,3)
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq
		                ) rpt1 on o.bgm_proj_seq = rpt1.bgm_proj_seq
		                and oe.bgm_source_seq = rpt1.bgm_source_seq
		                and oe.bgm_exp_seq3 = rpt1.bgm_exp_seq3
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq as bgm_exp_seq3
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
		                left join bgm_mas_expense e2 on e.upper_bgm_exp_seq = e2.bgm_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (4,5)
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq
		                ) rpt2 on o.bgm_proj_seq = rpt2.bgm_proj_seq
		                and oe.bgm_source_seq = rpt2.bgm_source_seq
		                and oe.bgm_exp_seq3 = rpt2.bgm_exp_seq3
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq as bgm_exp_seq3
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
		                left join bgm_mas_expense e2 on e.upper_bgm_exp_seq = e2.bgm_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (6)
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e2.upper_bgm_exp_seq
		                ) rpt3 on o.bgm_proj_seq = rpt3.bgm_proj_seq
		                and oe.bgm_source_seq = rpt3.bgm_source_seq
		                and oe.bgm_exp_seq3 = rpt3.bgm_exp_seq3
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq as bgm_exp_seq3
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
		                left join bgm_mas_expense e2 on e.upper_bgm_exp_seq = e2.bgm_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (7)
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq
		                ) rpt4 on o.bgm_proj_seq = rpt4.bgm_proj_seq
		                and oe.bgm_source_seq = rpt4.bgm_source_seq
		                and oe.bgm_exp_seq3 = rpt4.bgm_exp_seq3
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq as bgm_exp_seq3
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
		                left join bgm_mas_expense e2 on e.upper_bgm_exp_seq = e2.bgm_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (8)
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq
		                ) rpt5 on o.bgm_proj_seq = rpt5.bgm_proj_seq
		                and oe.bgm_source_seq = rpt5.bgm_source_seq
		                and oe.bgm_exp_seq3 = rpt5.bgm_exp_seq3
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq as bgm_exp_seq3
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
		                left join bgm_mas_expense e2 on e.upper_bgm_exp_seq = e2.bgm_exp_seq
		                where rpt.record_status not like 'D'
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e2.upper_bgm_exp_seq
		                ) rpt6 on o.bgm_proj_seq = rpt6.bgm_proj_seq
		                and oe.bgm_source_seq = rpt6.bgm_source_seq
		                and oe.bgm_exp_seq3 = rpt6.bgm_exp_seq3
	                left join fim_mas_record_statuses rs on o.record_status = rs.record_status

	                where o.record_status in ('A', 'C')"
                    + (!string.IsNullOrEmpty(bgm_proj_seq) ? " and o.bgm_proj_seq = @bgm_proj_seq" : "")
                    + (!string.IsNullOrEmpty(bgm_year) ? " and o.bgm_year = @bgm_year" : "")
                    + (!string.IsNullOrEmpty(job_site_seq) ? " and o.job_site_seq = @job_site_seq" : "")
                    + @")
	                union ALL
	                (
	                select concat('/', o.bgm_proj_seq, '/', oe.bgm_source_seq, '/', oe.bgm_exp_grp_seq, '/', oe.bgm_exp_seq3, '/', oe.bgm_exp_seq2) as child_seq
	                , concat('/', o.bgm_proj_seq, '/', oe.bgm_source_seq, '/', oe.bgm_exp_grp_seq, '/', oe.bgm_exp_seq3) as parent_seq
	                , oe.bgm_exp_name as name

	                , coalesce(oe.bath_amount, 0) as operation_amount
	                , coalesce(rpt1.cal_amount, 0) as transfer_amount
	                , coalesce(rpt2.cal_amount, 0) as reserve_amount
	                , coalesce(rpt3.cal_amount, 0) as moneyreq_amount
	                , coalesce(rpt4.cal_amount, 0) as pay_amount
	                , coalesce(rpt5.cal_amount, 0) as return_amount
	                , coalesce(rpt6.cal_amount, 0) as remain_amount
	                , null as record_status_name, null as record_status
	                from bgm_project o
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e1.bgm_exp_grp_seq

		                , e2.bgm_exp_seq as bgm_exp_seq2
		                , e2.upper_bgm_exp_seq as bgm_exp_seq3

		                , e2.bgm_exp_name
		                , sum(coalesce(oe.total_amount ,0)) as bath_amount
		                from bgm_project_expense oe
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e1 on pe.bgm_exp_seq = e1.bgm_exp_seq
		                left join bgm_mas_expense e2 on e1.upper_bgm_exp_seq = e2.bgm_exp_seq
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e1.bgm_exp_grp_seq, e2.bgm_exp_seq, e2.upper_bgm_exp_seq, e2.bgm_exp_name
		                ) oe on o.bgm_proj_seq = oe.bgm_proj_seq
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq as bgm_exp_seq2
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (2,3)
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq
		                ) rpt1 on o.bgm_proj_seq = rpt1.bgm_proj_seq
		                and oe.bgm_source_seq = rpt1.bgm_source_seq
		                and oe.bgm_exp_seq2 = rpt1.bgm_exp_seq2
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq as bgm_exp_seq2
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (4,5)
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq
		                ) rpt2 on o.bgm_proj_seq = rpt2.bgm_proj_seq
		                and oe.bgm_source_seq = rpt2.bgm_source_seq
		                and oe.bgm_exp_seq2 = rpt2.bgm_exp_seq2
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq as bgm_exp_seq2
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (6)
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e.upper_bgm_exp_seq
		                ) rpt3 on o.bgm_proj_seq = rpt3.bgm_proj_seq
		                and oe.bgm_source_seq = rpt3.bgm_source_seq
		                and oe.bgm_exp_seq2 = rpt3.bgm_exp_seq2
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq as bgm_exp_seq2
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (7)
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq
		                ) rpt4 on o.bgm_proj_seq = rpt4.bgm_proj_seq
		                and oe.bgm_source_seq = rpt4.bgm_source_seq
		                and oe.bgm_exp_seq2 = rpt4.bgm_exp_seq2
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq as bgm_exp_seq2
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (8)
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq
		                ) rpt5 on o.bgm_proj_seq = rpt5.bgm_proj_seq
		                and oe.bgm_source_seq = rpt5.bgm_source_seq
		                and oe.bgm_exp_seq2 = rpt5.bgm_exp_seq2
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq as bgm_exp_seq2
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
		                where rpt.record_status not like 'D'
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.upper_bgm_exp_seq
		                ) rpt6 on o.bgm_proj_seq = rpt6.bgm_proj_seq
		                and oe.bgm_source_seq = rpt6.bgm_source_seq
		                and oe.bgm_exp_seq2 = rpt6.bgm_exp_seq2
	                left join fim_mas_record_statuses rs on o.record_status = rs.record_status

	                where o.record_status in ('A', 'C')"
                    + (!string.IsNullOrEmpty(bgm_proj_seq) ? " and o.bgm_proj_seq = @bgm_proj_seq" : "")
                    + (!string.IsNullOrEmpty(bgm_year) ? " and o.bgm_year = @bgm_year" : "")
                    + (!string.IsNullOrEmpty(job_site_seq) ? " and o.job_site_seq = @job_site_seq" : "")
                    + @")
	                union ALL
	                (
	                select concat('/', o.bgm_proj_seq, '/', oe.bgm_source_seq, '/', oe.bgm_exp_grp_seq, '/', oe.bgm_exp_seq3, '/', oe.bgm_exp_seq2, '/', oe.bgm_exp_seq1) as child_seq
	                , concat('/', o.bgm_proj_seq, '/', oe.bgm_source_seq, '/', oe.bgm_exp_grp_seq, '/', oe.bgm_exp_seq3, '/', oe.bgm_exp_seq2) as parent_seq
	                , oe.bgm_exp_name as name

	                , coalesce(oe.bath_amount, 0) as operation_amount
	                , coalesce(rpt1.cal_amount, 0) as transfer_amount
	                , coalesce(rpt2.cal_amount, 0) as reserve_amount
	                , coalesce(rpt3.cal_amount, 0) as moneyreq_amount
	                , coalesce(rpt4.cal_amount, 0) as pay_amount
	                , coalesce(rpt5.cal_amount, 0) as return_amount
	                , coalesce(rpt6.cal_amount, 0) as remain_amount
	                , null as record_status_name, null as record_status
	                from bgm_project o
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e1.bgm_exp_grp_seq
		                , e1.bgm_exp_seq as bgm_exp_seq1
		                , e2.bgm_exp_seq as bgm_exp_seq2
		                , e2.upper_bgm_exp_seq as bgm_exp_seq3

		                , e1.bgm_exp_name
		                , sum(coalesce(oe.total_amount ,0)) as bath_amount
		                from bgm_project_expense oe
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e1 on pe.bgm_exp_seq = e1.bgm_exp_seq
		                left join bgm_mas_expense e2 on e1.upper_bgm_exp_seq = e2.bgm_exp_seq

		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e1.bgm_exp_grp_seq, e1.bgm_exp_seq, e2.bgm_exp_seq, e2.upper_bgm_exp_seq, e1.bgm_exp_name
		                ) oe on o.bgm_proj_seq = oe.bgm_proj_seq
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq as bgm_exp_seq1
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (2,3)
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq
		                ) rpt1 on o.bgm_proj_seq = rpt1.bgm_proj_seq
		                and oe.bgm_source_seq = rpt1.bgm_source_seq
		                and oe.bgm_exp_seq1 = rpt1.bgm_exp_seq1
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq as bgm_exp_seq1
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (4,5)
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq
		                ) rpt2 on o.bgm_proj_seq = rpt2.bgm_proj_seq
		                and oe.bgm_source_seq = rpt2.bgm_source_seq
		                and oe.bgm_exp_seq1 = rpt2.bgm_exp_seq1
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq as bgm_exp_seq1
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (6)
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq
		                ) rpt3 on o.bgm_proj_seq = rpt3.bgm_proj_seq
		                and oe.bgm_source_seq = rpt3.bgm_source_seq
		                and oe.bgm_exp_seq1 = rpt3.bgm_exp_seq1
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq as bgm_exp_seq1
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (7)
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq
		                ) rpt4 on o.bgm_proj_seq = rpt4.bgm_proj_seq
		                and oe.bgm_source_seq = rpt4.bgm_source_seq
		                and oe.bgm_exp_seq1 = rpt4.bgm_exp_seq1
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq as bgm_exp_seq1
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
		                where rpt.record_status not like 'D'
		                and rpt.rpt_type in (8)
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq
		                ) rpt5 on o.bgm_proj_seq = rpt5.bgm_proj_seq
		                and oe.bgm_source_seq = rpt5.bgm_source_seq
		                and oe.bgm_exp_seq1 = rpt5.bgm_exp_seq1
	                left join (select oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq as bgm_exp_seq1
		                , sum(coalesce(rpt.cal_amount,0)) as cal_amount
		                from bgm_project_report rpt
		                inner join bgm_project_expense oe on rpt.bgm_proj_exp_seq = oe.bgm_proj_exp_seq
			                
		                inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
		                left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
		                where rpt.record_status not like 'D'
		                group by oe.bgm_proj_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq, e.bgm_exp_seq
		                ) rpt6 on o.bgm_proj_seq = rpt6.bgm_proj_seq
		                and oe.bgm_source_seq = rpt6.bgm_source_seq
		                and oe.bgm_exp_seq1 = rpt6.bgm_exp_seq1
	                left join fim_mas_record_statuses rs on o.record_status = rs.record_status

	                where o.record_status in ('A', 'C')"
                    + (!string.IsNullOrEmpty(bgm_proj_seq) ? " and o.bgm_proj_seq = @bgm_proj_seq" : "")
                    + (!string.IsNullOrEmpty(bgm_year) ? " and o.bgm_year = @bgm_year" : "")
                    + (!string.IsNullOrEmpty(job_site_seq) ? " and o.job_site_seq = @job_site_seq" : "")
	                + ")";
                #endregion

                DataTable dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public double GetProjectExpenseControlAmount(string bgm_strat_list_seq, string job_site_seq, string bgm_source_seq, string bgm_exp_grp_seq)
        {
            double dAmount = 0;
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@bgm_strat_list_seq", bgm_strat_list_seq);
                dbManager.AddParameters(1, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@bgm_exp_grp_seq", bgm_exp_grp_seq);

                string strGroupBy = (!string.IsNullOrEmpty(bgm_strat_list_seq) ? ", p.bgm_strat_list_seq" : "") + (!string.IsNullOrEmpty(bgm_strat_list_seq) ? ", p.job_site_seq" : "");

                string sql = "SELECT pe.bgm_source_seq, exp.bgm_exp_grp_seq"
                    + strGroupBy
                    + ", sum(coalesce(ec.amount, 0)) as amount"

                    + " from bgm_project_expense_control ec"
                    + " left join bgm_project_expense pe on ec.bgm_proj_exp_seq = pe.bgm_proj_exp_seq"
                    + " left join bgm_project p on pe.bgm_proj_seq = p.bgm_proj_seq"
	                    + " and p.record_status not like 'D'"
                    + " left join bgm_mas_expense exp on pe.bgm_exp_seq = exp.bgm_exp_seq"
                    + " where ec.record_status not like 'D'"
                    + (!string.IsNullOrEmpty(bgm_strat_list_seq) ? " and p.bgm_strat_list_seq = @bgm_strat_list_seq" : "")
                    + (!string.IsNullOrEmpty(job_site_seq) ? " and p.job_site_seq = @job_site_seq" : "")
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " and pe.bgm_source_seq = @bgm_source_seq" : "")
                    + (!string.IsNullOrEmpty(bgm_exp_grp_seq) ? " and exp.bgm_exp_grp_seq = @bgm_exp_grp_seq" : "")
                    + " group by pe.bgm_source_seq, exp.bgm_exp_grp_seq"
                    + strGroupBy;

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];

                if (dt.Rows.Count > 0)
                {
                    dAmount = Convert.ToDouble(dt.Rows[0]["amount"]);
                }

                return dAmount;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public double GetProjectExpenseControlAmountBySeq(string bgm_proj_exp_ctrl_seq)
        {
            double dAmount = 0;
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@bgm_proj_exp_ctrl_seq", bgm_proj_exp_ctrl_seq);

                string sql = "SELECT ec.bgm_proj_exp_ctrl_seq"
                    + ", coalesce(ec.amount, 0) + coalesce(ec2.amount, 0) as amount"

                    + " from bgm_project_expense_control ec"
                    + " left join (select from_bgm_proj_exp_ctrl_seq"
                        + ", sum(coalesce(amount, 0)) as amount"
                        + " from bgm_project_expense_control"
                        + " where record_status not like 'D'"
                        + " group by from_bgm_proj_exp_ctrl_seq"
                        + ") ec2 on ec.bgm_proj_exp_ctrl_seq = ec2.from_bgm_proj_exp_ctrl_seq"
                    + " where ec.bgm_proj_exp_ctrl_seq = @bgm_proj_exp_ctrl_seq";
                    //+ " group by ec.bgm_proj_exp_ctrl_seq";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];

                if (dt.Rows.Count > 0)
                {
                    dAmount = Convert.ToDouble(dt.Rows[0]["amount"]);
                }

                return dAmount;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetProjectExpenseControlData(string bgm_proj_exp_ctrl_seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@bgm_proj_exp_ctrl_seq", bgm_proj_exp_ctrl_seq);

                string sql = "SELECT ec.bgm_proj_exp_ctrl_seq, ec.bgm_proj_exp_seq, ec.ctrl_type"
                    + ", ec.send_or_receive, ec.amount"
                    + ", p.bgm_proj_name, exp.bgm_exp_name"

                    + " from bgm_project_expense_control ec"
                    + " left join bgm_project_expense pe on ec.bgm_proj_exp_seq = pe.bgm_proj_exp_seq"
                    + " left join bgm_project p on pe.bgm_proj_seq = p.bgm_proj_seq"
                    + " left join bgm_mas_expense exp on pe.bgm_exp_seq = exp.bgm_exp_seq"
                    + " where ec.bgm_proj_exp_ctrl_seq = @bgm_proj_exp_ctrl_seq";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetTransferReport(string seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@seq", seq);

                string sql = "select re.bgm_proj_exp_seq, exp.bgm_exp_name"
                    + ", exp.bgm_exp_grp_seq, expgrp.bgm_exp_grp_name"
                    + ", re.bgm_source_seq, s.bgm_source_name"
                    + ", rt.created_when, rt.cal_amount"
                    + " from bgm_project r"
                        + " left join bgm_project_expense re on re.bgm_proj_seq = r.bgm_proj_seq"
                        + " left join bgm_project_report rt on rt.bgm_proj_exp_seq = re.bgm_proj_exp_seq"
                        + " left join bgm_mas_expense exp on re.bgm_exp_seq = exp.bgm_exp_seq"
                        + " left join bgm_mas_source s on re.bgm_source_seq = s.bgm_source_seq"
                        + " left join bgm_mas_expense_group expgrp on exp.bgm_exp_grp_seq = expgrp.bgm_exp_grp_seq"
                    + " where r.bgm_proj_seq = @seq"
                        + " and rt.rpt_type in (2, 3)"
                        + " and rt.record_status <> 'D'";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateProject(string bgm_proj_seq, string bgm_proj_code, string bgm_proj_name
            , string bgm_proj_descr, string bgm_proj_manager, string bgm_proj_scope
            , string bgm_proj_reason, string bgm_proj_object, string bgm_proj_target
            , string bgm_proj_plan, string bgm_proj_remark, string prj_type_seq
            , DateTime date_start, DateTime date_end, string gfmis_code, string UpdatedBy)
        {
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(21);
                dbManager.AddParameters(0, "@seq", bgm_proj_seq);
                dbManager.AddParameters(1, "@bgm_proj_code", bgm_proj_code);
                dbManager.AddParameters(2, "@bgm_proj_name", bgm_proj_name);
                dbManager.AddParameters(3, "@bgm_proj_descr", bgm_proj_descr);
                dbManager.AddParameters(4, "@bgm_proj_manager", bgm_proj_manager);
                dbManager.AddParameters(5, "@bgm_proj_scope", bgm_proj_scope);
                dbManager.AddParameters(6, "@bgm_proj_date", "");
                dbManager.AddParameters(7, "@bgm_proj_reason", bgm_proj_reason);
                dbManager.AddParameters(8, "@bgm_proj_object", bgm_proj_object);
                dbManager.AddParameters(9, "@bgm_proj_target", bgm_proj_target);
                dbManager.AddParameters(10, "@bgm_proj_plan", bgm_proj_plan);
                dbManager.AddParameters(11, "@bgm_proj_remark", bgm_proj_remark);
                dbManager.AddParameters(12, "@bgm_strat_list_seq", "");
                dbManager.AddParameters(13, "@prj_type_seq", prj_type_seq);
                dbManager.AddParameters(14, "@job_site_seq", "");
                dbManager.AddParameters(15, "@bgm_year", "");
                dbManager.AddParameters(16, "@date_start", date_start);
                dbManager.AddParameters(17, "@date_end", date_end);
                dbManager.AddParameters(18, "@gfmis_code", gfmis_code);

                dbManager.AddParameters(19, "@updated_by", UpdatedBy);
                dbManager.AddParameters(20, "@updated_when", DateTime.Now);

                string strsql = "UPDATE bgm_project"
                    + " SET bgm_proj_code = @bgm_proj_code"
                        + ", bgm_proj_name = @bgm_proj_name"
                        + ", bgm_proj_descr = @bgm_proj_descr"
                        + ", bgm_proj_manager = @bgm_proj_manager"
                        + ", bgm_proj_scope = @bgm_proj_scope"
                        //+ ", bgm_proj_date = @bgm_proj_date"
                        + ", bgm_proj_reason = @bgm_proj_reason"
                        + ", bgm_proj_object = @bgm_proj_object"
                        + ", bgm_proj_target = @bgm_proj_target"
                        + ", bgm_proj_plan = @bgm_proj_plan"
                        + ", bgm_proj_remark = @bgm_proj_remark"
                        //+ ", bgm_strat_list_seq = @bgm_strat_list_seq"
                        + ", prj_type_seq = @prj_type_seq"
                        //+ ", job_site_seq = @job_site_seq"
                        //+ ", bgm_year = @bgm_year"
                        + ", date_start = @date_start"
                        + ", date_end = @date_end"
                        + ", gfmis_code = @gfmis_code"
                        + ", updated_by = @updated_by"
                        + ", updated_when = @updated_when"
                    + " WHERE bgm_proj_seq = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string InsertProjectExpenseControl(string from_bgm_proj_exp_ctrl_seq, string bgm_proj_exp_seq
            , string ctrl_type, string send_or_receive, string amount, string CreatedBy)
        {
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@from_bgm_proj_exp_ctrl_seq", from_bgm_proj_exp_ctrl_seq);
                dbManager.AddParameters(1, "@bgm_proj_exp_seq", bgm_proj_exp_seq);
                dbManager.AddParameters(2, "@ctrl_type", ctrl_type);
                dbManager.AddParameters(3, "@send_or_receive", send_or_receive);
                dbManager.AddParameters(4, "@amount", amount);

                dbManager.AddParameters(5, "@created_by", CreatedBy);
                dbManager.AddParameters(6, "@created_when", DateTime.Now);

                string strsql = "Insert into bgm_project_expense_control"
                    + " (from_bgm_proj_exp_ctrl_seq, bgm_proj_exp_seq, ctrl_type, send_or_receive, amount"
                    + ", record_status, created_by, created_when)"
                    + " values(@from_bgm_proj_exp_ctrl_seq, @bgm_proj_exp_seq, @ctrl_type, @send_or_receive, @amount"
                    + ", 'U', @created_by, @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strsql);

                string strsqlLast_insert = "select distinct scope_identity() FROM bgm_project_expense_control";
                string strLastSeq = dbManager.ExecuteDataSet(CommandType.Text, strsqlLast_insert).Tables[0].Rows[0][0].ToString();
                dbManager.CommitTransaction();
                return strLastSeq;
            }
            catch (Exception ex)
            {
                dbManager.RollBackTransaction();
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void MoveProjectToNewJobSite(string bgm_proj_seq, string job_site_seq, string emp_seq)
        {
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@bgm_proj_seq", bgm_proj_seq);
                dbManager.AddParameters(1, "@job_site_seq", job_site_seq);

                dbManager.AddParameters(2, "@updated_by", emp_seq);
                dbManager.AddParameters(3, "@updated_when", DateTime.Now);

                string strSQL = "update bgm_project p"
                    + " left join bgm_project_operation o on p.bgm_proj_seq = o.bgm_proj_seq"
                    + " left join bgm_project_operation_expense oe on o.bgm_proj_oper_seq = oe.bgm_proj_oper_seq"
                    + " left join fim_money_requests mr on o.bgm_proj_oper_seq = mr.bgm_proj_oper_seq"
                    + " left join fim_money_request_dtls mrd on mr.money_req_seq = mrd.money_req_seq"
                    + " left join fim_petition_dtls pet on mr.money_req_seq = pet.money_req_seq"

                    + " left join icm_pre_purchase_request_item ppri on oe.bgm_proj_oper_exp_seq = ppri.bgm_proj_oper_exp_seq"
                    + " left join icm_pre_purchase_request ppr on ppri.icm_ppr_seq = ppr.icm_ppr_seq"

                    + " left join icm_purchase_request_item pri on ppri.icm_pr_item_seq = pri.icm_pr_item_seq"
                    + " left join icm_purchase_request pr on pri.icm_pr_seq = pr.icm_pr_seq"

                    + " left join icm_bid b on pr.icm_pr_seq = b.icm_pr_seq"
                    + " left join icm_bidder bd on bd.icm_bid_seq = bd.icm_bid_seq"

                    + " left join icm_purchase_order po on bd.icm_bidder_seq = po.icm_bidder_seq"
                    + " left join icm_delivery_note dn on po.icm_po_seq = dn.icm_po_seq"
                    + " left join icm_receive rec on dn.icm_dn_seq = rec.icm_dn_seq"

                    + " set p.job_site_seq = @job_site_seq"
                    + ", p.updated_by = @updated_by"
                    + ", p.updated_when = @updated_when"

                    + ", o.job_site_seq = @job_site_seq"
                    + ", o.updated_by = @updated_by"
                    + ", o.updated_when = @updated_when"

                    + ", mr.job_site_seq = @job_site_seq"
                    + ", mr.updated_by = @updated_by"
                    + ", mr.updated_when = @updated_when"

                    + ", mrd.job_site_seq = @job_site_seq"
                    + ", mrd.updated_by = @updated_by"
                    + ", mrd.updated_when = @updated_when"

                    + ", pet.job_site_seq = @job_site_seq"
                    + ", pet.updated_by = @updated_by"
                    + ", pet.updated_when = @updated_when"

                    + ", ppr.job_site_seq = @job_site_seq"
                    + ", ppr.job_site_direct_seq = @job_site_seq"
                    + ", ppr.updated_by = @updated_by"
                    + ", ppr.updated_when = @updated_when"

                    + ", pr.purchase_job_site_seq = @job_site_seq"
                    + ", pr.updated_by = @updated_by"
                    + ", pr.updated_when = @updated_when"

                    + ", b.bid_job_site_seq = @job_site_seq"
                    + ", b.updated_by = @updated_by"
                    + ", b.updated_when = @updated_when"

                    + ", po.order_job_site_seq = @job_site_seq"
                    + ", po.updated_by = @updated_by"
                    + ", po.updated_when = @updated_when"

                    + ", dn.delivery_job_site_seq = @job_site_seq"
                    + ", dn.updated_by = @updated_by"
                    + ", dn.updated_when = @updated_when"

                    + ", rec.job_site_seq = @job_site_seq"
                    + ", rec.updated_by = @updated_by"
                    + ", rec.updated_when = @updated_when"

                    + " where p.bgm_proj_seq = @bgm_proj_seq";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                //strSQL = "update bgm_project_operation"
                //    + " set job_site_seq = @job_site_seq"
                //    + ", updated_by = @updated_by"
                //    + ", updated_when = @updated_when"
                //    + " where bgm_proj_seq = @bgm_proj_seq";
                //dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                //strSQL = "update fim_money_request_dtls mrd"
                //    + " left join fim_money_requests mr on mrd.money_req_seq = mr.money_req_seq"
                //    + " left join bgm_project_operation o on mrd.money_req_seq = mr.money_req_seq"
                //    + " set o.job_site_seq = @job_site_seq"
                //    + ", o.updated_by = @updated_by"
                //    + ", o.updated_when = @updated_when"
                //    + " where o.bgm_proj_seq = @bgm_proj_seq";
                //dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                dbManager.CommitTransaction();
            }
            catch (Exception ex)
            {
                dbManager.RollBackTransaction();
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        //public void CloseProject(string bgm_proj_seq, string UpdatedBy)
        //{
        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.CreateParameters(3);
        //        dbManager.AddParameters(0, "@bgm_proj_seq", bgm_proj_seq);

        //        dbManager.AddParameters(1, "@updated_by", UpdatedBy);
        //        dbManager.AddParameters(2, "@updated_when", DateTime.Now);

        //        string strSQL = "UPDATE bgm_project"
        //            + " SET is_use = 0"
        //                + ", bgm_proj_name = @bgm_proj_name"
        //                + ", bgm_proj_descr = @bgm_proj_descr"
        //                + ", bgm_proj_manager = @bgm_proj_manager"
        //                + ", bgm_proj_scope = @bgm_proj_scope"
        //            //+ ", bgm_proj_date = @bgm_proj_date"
        //                + ", bgm_proj_reason = @bgm_proj_reason"
        //                + ", bgm_proj_object = @bgm_proj_object"
        //                + ", bgm_proj_target = @bgm_proj_target"
        //                + ", bgm_proj_plan = @bgm_proj_plan"
        //                + ", bgm_proj_remark = @bgm_proj_remark"
        //            //+ ", bgm_strat_list_seq = @bgm_strat_list_seq"
        //                + ", prj_type_seq = @prj_type_seq"
        //            //+ ", job_site_seq = @job_site_seq"
        //            //+ ", bgm_year = @bgm_year"
        //                + ", date_start = @date_start"
        //                + ", date_end = @date_end"
        //                + ", gfmis_code = @gfmis_code"
        //                + ", updated_by = @updated_by"
        //                + ", updated_when = @updated_when"
        //            + " WHERE bgm_proj_seq = @seq";

        //        dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }
        //}
    }

    public class ProjectReportModel
    {
        public string bgm_proj_code { get; set; }
        
        public string bgm_proj_name { get; set; }
        
        public string bgm_exp_name1 { get; set; }
        
        public string bgm_exp_name2 { get; set; }
        
        public string bgm_exp_name3 { get; set; }
        
        public string bgm_exp_grp_name { get; set; }
        
        public string bgm_source_name { get; set; }
        
        public double project_amount { get; set; }

        public double transfer_amount { get; set; }

        public double operation_return_amount { get; set; }

        public double operation_amount { get; set; }

        public double icm_amount { get; set; }

        public double money_req_amount { get; set; }

        public double pay_amount { get; set; }

        public double money_return_amount { get; set; }
    }

    public class ViewReportModel
    {
        public string code { get; set; }

        public string name { get; set; }

        public double project_amount { get; set; }

        public double transfer_amount { get; set; }

        public double operation_return_amount { get; set; }

        public double operation_amount { get; set; }

        public double icm_amount { get; set; }

        public double money_req_amount { get; set; }

        public double pay_amount { get; set; }

        public double money_return_amount { get; set; }

        public ManageProject.ProjectDataType type { get; set; }
    }
}
